可能是重复问题:
如何使 SQL 级别的函数对 LINQ to Entity 查询可用?
我有一个标量函数,用于获取两点之间的距离,并希望使用它查询最靠近该点的记录。该标量函数可以与 LINQ to 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实体模型,从数据库更新了模型并选择了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>
我创建了一个部分类,并编写了以下方法。
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);
当我尝试对NParcel进行计数或foreach时,我遇到了这个错误:
“类型'EFSample.GeoDataEntities'中指定的方法'Double DistanceBetween(Double,Double,Double,Double)'无法转换为LINQ to Entities存储表达式。”
并且有一个堆栈跟踪:
在System.Data.Objects.ELinq.ExpressionConverter.ThrowUnresolvableFunction(Expression Expression)处抛出未解决的函数异常......在System.Linq.Queryable.Count[TSource](IQueryable1 source)处抛出异常。