C# 如何在 linq to entity 中使用标量值函数？

How to use scalar-valued function with linq to entity?

提问by Khalid Omar

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

可能的重复：
SQL 级别的函数是否可用于 LINQ to Entity 查询？

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

我有一个标量函数，可以获取两点之间的距离，我想用它来查询距离最近的记录。标量函数与 linq to sql 一起工作，但与 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

我添加了一个 ado.net 实体模型，从数据库更新了模型并选择了 distance between

 <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 countor foreachthe NParcel I get this error

当我尝试count或foreachNParcel 时出现此错误

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

无法将类型“EFSample.GeoDataEntities”上的指定方法“Double DistanceBetween(Double, Double, Double, Double)”转换为 LINQ to Entities 存储表达式。

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)

在 System.Data.Objects.ELinq.ExpressionConverter.ThrowUnresolvableFunction(Expression Expression) 在 System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FunctionCallTranslator.TranslateFunctionCall(ExpressionConverter parent, MethodCallExpression call, EdmFunctionAttribute functionAttribute) at System.ELinq. .ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) 在 System.Data.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq) 在 System.Data.Objects.ELinq.ExpressionExpression(ExpresslinqTranslate)在 System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input) 在 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(Expression SystemCallExpression) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionSystemCallExpression call) .Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) 在 System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) 在 System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.AggregateTranslator. ExpressionConverter 父级，MethodCallExpression 调用）在 System.Data.Objects.ELinq.ExpressionConverter。MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) 在 System.Data.Objects.ELinq.ExpressionConverter.Convert() 在 System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable)1 forMergeOption) at System.Data.Objects.ObjectQuery1.GetResults(Nullable 1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable.GetEnumerator() 在 System.Linq.Enumerable.Single[TSource](IEnumerable 1 source) at System.Linq.Queryable.Count[TSource](IQueryable1 源)

采纳答案by Stacker

Here is how you do it:

这是你如何做到的：

Step 1: In edmx

第 1 步：在 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

第 2 步：导入函数

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

1. 双击 edmx
2. 在模型浏览器视图中，展开GeoDataModel.Store（可以命名不同）
3. 扩张 stored procedures /function
4. 双击 DistanceBetween
5. Scalars = Single
6. 单击确定

Step 3: In C#:

第 3 步：在 C# 中：

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

---

---

notethat IsComposable="false"and no ReturnTypeand dont forget to add the :

请注意， IsComposable="false"不要ReturnType，不要忘记添加：

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

Hope that help....

希望有所帮助....