How to use scalar-valued function with linq to entity? [duplicate]

Question

Possible Duplicate:
Can SQL level functions be made available to LINQ to Entity queries?

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

the scalar function

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

I added an ado.net entity model, updated the model from the database and chose distancebetween

 <Function Name="DistanceBetween" ReturnType="real" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
      <Parameter Name="Lat1" Type="real" Mode="In" />
      <Parameter Name="Long1" Type="real" Mode="In" />
      <Parameter Name="Lat2" Type="real" Mode="In" />
      <Parameter Name="Long2" Type="real" Mode="In" />
    </Function>

I made a partial class and wrote this method

public partial class GeoDataEntities
{
    [EdmFunction("GeoDataModel.Store", "DistanceBetween")]
    public double DistanceBetween(double lat1, double lon1, double lat2, double lon2)
    {
        throw new NotImplementedException();
    }
}

I tried many times to query the function with this code but it gets an error

var NParcel = db.geoAddresses.Where(g=> db.DistanceBetween(21.5,39.5, g.lat,g.lon) < 20);

when i try to count or foreach the NParcel I get this error

The specified method 'Double DistanceBetween(Double, Double, Double, Double)' on the type 'EFSample.GeoDataEntities' cannot be translated into a LINQ to Entities store expression.

and stacktrace

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& source, DbExpressionBinding& sourceBinding, DbExpression& 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(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable.GetEnumerator() at System.Linq.Enumerable.Single[TSource](IEnumerable1 source) at System.Linq.Queryable.Count[TSource](IQueryable1 source)

Amalee

EF5 supports spatial data type (blogs.msdn.com/b/efdesign/archive/2011/05/04/…) out of the box. – Commandment

the problem not in spatial data the problem that i have many scalar function i want to use – Amalee

Gert: i follow that link #10626455 and i modified the edmx and the problem didn't solved – Amalee

Answer 1

Here is how you do it:

Step 1: In edmx

      <Function Name="DistanceBetween" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
      <CommandText>
        select dbo.DistanceBetween(@lat1,@long1,@lat2,@long2)
      </CommandText>
      <Parameter Name="Lat1" Type="real" Mode="In" />
      <Parameter Name="Long1" Type="real" Mode="In" />
      <Parameter Name="Lat2" Type="real" Mode="In" />
      <Parameter Name="Long2" Type="real" Mode="In" />
    </Function>

step 2: Import the function

1. double click the edmx
2. In the Model Browser view, expand GeoDataModel.Store (could be named different)
3. expand stored procedures /function
4. double click DistanceBetween
5. Scalars = Single
6. Click OK

Step 3: In C#:

    GeoDataEntities db = new GeoDataEntities();
    var first = db.DistanceBetween(234, 2342, 424, 243).First().Value;

---

note that IsComposable="false" and no ReturnType and dont forget to add the :

      <CommandText>
        select dbo.DistanceBetween(@lat1,@long1,@lat2,@long2)
      </CommandText>

Hope that help....

Redwine

This seemed to work. But when I refreshed the model (for other objects), it changed back to Composable, which gave errors. Not compile errors, so it may still work, but doesn't seem like good long term approach. Every function giving errors in the model. Guess creating SPROCs to call Functions is still my approach. – Pleomorphism