title: How to use scalar-valued function with linq to entity?
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/10625955/can-sql-level-functions-be-made-available-to-linq-to-entity-queries">Can SQL level functions be made available to LINQ to Entity queries?</a>  </p>
  </blockquote>
  
  
  
  <p>I have a scalar function that gets the distance between two points and I want to use it to query closest record to point.
  The scalar function works with linq to sql but fails with EF</p>
  
  <p>the scalar function </p>
  
  <pre><code>USE [GeoData]
  GO
  
  /****** Object:  UserDefinedFunction [dbo].[DistanceBetween]    Script Date: 09/18/2012 19:40:44 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  
  CREATE FUNCTION [dbo].[DistanceBetween](@Lat1 as real,
  @Long1 as real, @Lat2 as real, @Long2 as real)
  RETURNS real
  AS
  BEGIN
  
  DECLARE @dLat1InRad as float(53);
  SET @dLat1InRad = @Lat1;
  DECLARE @dLong1InRad as float(53);
  SET @dLong1InRad = @Long1;
  DECLARE @dLat2InRad as float(53);
  SET @dLat2InRad = @Lat2;
  DECLARE @dLong2InRad as float(53);
  SET @dLong2InRad = @Long2 ;
  
  DECLARE @dLongitude as float(53);
  SET @dLongitude = @dLong2InRad - @dLong1InRad;
  DECLARE @dLatitude as float(53);
  SET @dLatitude = @dLat2InRad - @dLat1InRad;
  /* Intermediate result a. */
  DECLARE @a as float(53);
  SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad)
  * COS (@dLat2InRad)
  * SQUARE(SIN (@dLongitude / 2.0));
  /* Intermediate result c (great circle distance in Radians). */
  DECLARE @c as real;
  SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a));
  DECLARE @kEarthRadius as real;
  /* SET kEarthRadius = 3956.0 miles */
  SET @kEarthRadius = 6376.5;        /* kms */
  
  DECLARE @dDistance as real;
  SET @dDistance = @kEarthRadius * @c;
  return (@dDistance);
  END
  
  GO
  </code></pre>
  
  <p>I added an ado.net entity model, updated the model from the database and chose distancebetween</p>
  
  <pre><code> &lt;Function Name="DistanceBetween" ReturnType="real" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"&gt;
        &lt;Parameter Name="Lat1" Type="real" Mode="In" /&gt;
        &lt;Parameter Name="Long1" Type="real" Mode="In" /&gt;
        &lt;Parameter Name="Lat2" Type="real" Mode="In" /&gt;
        &lt;Parameter Name="Long2" Type="real" Mode="In" /&gt;
      &lt;/Function&gt;
  </code></pre>
  
  <p>I made a partial class and wrote this method</p>
  
  <pre><code>public partial class GeoDataEntities
  {
      [EdmFunction("GeoDataModel.Store", "DistanceBetween")]
      public double DistanceBetween(double lat1, double lon1, double lat2, double lon2)
      {
          throw new NotImplementedException();
      }
  }
  </code></pre>
  
  <p>I tried many times to query the function with this code but it gets an error</p>
  
  <pre><code>var NParcel = db.geoAddresses.Where(g=&gt; db.DistanceBetween(21.5,39.5, g.lat,g.lon) &lt; 20);
  </code></pre>
  
  <p>when i try to <code>count</code> or <code>foreach</code> the NParcel I get this error </p>
  
  <blockquote>
    <p>The specified method 'Double DistanceBetween(Double, Double, Double,
    Double)' on the type 'EFSample.GeoDataEntities' cannot be translated
    into a LINQ to Entities store expression.</p>
  </blockquote>
  
  <p>and stacktrace</p>
  
  <blockquote>
    <p>at
    System.Data.Objects.ELinq.ExpressionConverter.ThrowUnresolvableFunction(Expression
    Expression)    at
    System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FunctionCallTranslator.TranslateFunctionCall(ExpressionConverter
    parent, MethodCallExpression call, EdmFunctionAttribute
    functionAttribute)    at
    System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter
    parent, MethodCallExpression linq)    at
    System.Data.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter
    parent, BinaryExpression linq)    at
    System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression
    linq)    at
    System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression
    lambda, DbExpression input)    at
    System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter
    parent, MethodCallExpression call, DbExpression&amp; source,
    DbExpressionBinding&amp; sourceBinding, DbExpression&amp; lambda)    at
    System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter
    parent, MethodCallExpression call)    at
    System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter
    parent, MethodCallExpression linq)    at
    System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression
    linq)    at
    System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.AggregateTranslator.Translate(ExpressionConverter
    parent, MethodCallExpression call)    at
    System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter
    parent, MethodCallExpression linq)    at
    System.Data.Objects.ELinq.ExpressionConverter.Convert()    at
    System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable<code>1
    forMergeOption)    at
    System.Data.Objects.ObjectQuery</code>1.GetResults(Nullable<code>1
    forMergeOption)    at
    System.Data.Objects.ObjectQuery</code>1.System.Collections.Generic.IEnumerable.GetEnumerator()
    at System.Linq.Enumerable.Single[TSource](IEnumerable<code>1 source)    at
    System.Linq.Queryable.Count[TSource](IQueryable</code>1 source)</p>
  </blockquote>