如何将标量值函数与linq一起用于实体

Question

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

我有一个标量函数，它可以获得两点之间的距离，我想用它来查询离点最近的记录。标量函数与linq到sql一起工作，但与EF 一起失败

标量函数

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

我添加了一个ado.net实体模型，从数据库中更新了模型，并选择了之间的距离

<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>

我做了一个分部类并编写了这个方法

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

我试过很多次用这个代码查询函数，但它得到了一个错误

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

当我尝试count或foreachNParcel时，我会得到这个错误

指定的方法'Double DistanceBetween(Double，Double，无法翻译类型"EFSample.GeoDataEntities"的"Double)"转换为LINQ to Entities存储表达式。

和堆栈跟踪

System.Data.Objects.ELinq.EExpressionConverter.ThrowUnsolableFunction(表达式表达式)System.Data.Objects.ELinq.EExpressionConverter.MethodCallTranslator.FunctionCallTranslator.PranslateFunctionCall(ExpressionConverter父级，MethodCallExpression调用，EdmFunctionAttributefunctionAttribute)System.Data.Objects.ELinq.EExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter父级，MethodCallExpression linq)System.Data.Objects.ELinq.EExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter父级，BinaryExpression linq)System.Data.Objects.ELinq.EExpressionConverter.TranslateExpression(表达式linq)System.Data.Objects.ELinq.EExpressionConverter.TranslateLambda(LambdaExpressionlambda，DbExpression输入)System.Data.Objects.ELinq.EExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter父级，MethodCallExpression调用，DbExpression&来源DbExpressionBinding&sourceBinding，DbExpression&λ)System.Data.Objects.ELinq.EExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter父级，MethodCallExpression调用)System.Data.Objects.ELinq.EExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter父级，MethodCallExpression linq)System.Data.Objects.ELinq.EExpressionConverter.TranslateExpression(表达式linq)System.Data.Objects.ELinq.EExpressionConverter.MethodCallTranslator.AgregateTranslator.Translate(ExpressionConverter父级，MethodCallExpression调用)System.Data.Objects.ELinq.EExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter父级，MethodCallExpression linq)位于的System.Data.Objects.ELinq.EExpressionConverter.Convert()System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(可为Null的1 forMergeOption) at System.Data.Objects.ObjectQuery1.GetResults(可为null的1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerator()在System.Linq.Enumerable.Single[TSource](IEnumerable1 source) at System.Linq.Queryable.Count[TSource](IQueryable1源)

Answer 1

以下是您的操作方法：

步骤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>

步骤2：导入函数

1. 双击edmx
2. 在"模型浏览器"视图中，展开GeoDataModel.Store(可以命名为其他名称)
3. 展开stored procedures /function
4. 双击DistanceBetween
5. Scalars = Single
6. 单击"确定">

步骤3：在C#中：

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

---

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

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

希望有帮助。。。。