如何使用标量值函数使用LINQ到实体？ [英] How to use scalar-valued function with linq to entity?

查看：121
本文介绍了如何使用标量值函数使用LINQ到实体？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述


可能重复：结果
的可以在SQL级别的功能提供给LINQ到实体的查询？






我有一个标量函数，得到两点之间的距离，我想用它来查询记录最接近点。
标量函数使用LINQ工程SQL，但无法与EF



标量函数

 使用[地理数据] 
 GO 
 
 / ******对象：UserDefinedFunction [DBO] [DistanceBetween]脚本日期：2012年9月18日19时40分44秒****** / 
 SET ANSI_NULLS ON 
 GO 
 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 
 
 CREATE FUNCTION [DBO]。[DistanceBetween]（@ LAT1以假乱真，
 @ Long1以假乱真，@ LAT2以假乱真，@ Long2以假乱真）
 RETURNS真正的
 AS 
 BEGIN 
 
 DECLARE @ dLat1InRad为float（53）; 
 SET @ dLat1InRad = @ LAT1; 
 DECLARE @ dLong1InRad为float（53）; 
 SET @ dLong1InRad = @ Long1; 
 DECLARE @ dLat2InRad为float（53）; 
 SET @ dLat2InRad = @ LAT2; 
 DECLARE @ dLong2InRad为float（53）; 
 SET @ dLong2InRad = @ Long2; 
 
 DECLARE @dLongitude为float（53）; 
设置@dLongitude = @ dLong2InRad  -  @ dLong1InRad; 
 DECLARE @dLatitude为float（53）; 
设置@dLatitude = @ dLat2InRad  -  @ dLat1InRad; 
 / *中间结果。 * / 
 DECLARE @a为float（53）; 
设置@a = SQUARE（SIN（@dLatitude / 2.0））+ COS（@ dLat1InRad）
 * COS（@ dLat2InRad）
 * SQUARE（SIN（@dLongitude / 2.0））; 
 / *中间结果C（弧度大圈的距离）。 * / 
 DECLARE @c为实; 
设置@c = 2.0 * ATN2（SQRT（@a），SQRT（1.0  -  @a））; 
 DECLARE @kEarthRadius以假乱真; 
 / *设置kEarthRadius =3956.0英里* / 
设置@kEarthRadius = 6376.5; / * *公里/ 
 
 DECLARE @dDistance以假乱真; 
设置@dDistance = @kEarthRadius * @c; 
回报（@dDistance）; 
端
 
 GO



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

 <作用NAME =distanceBetween的返回类型=真正的集合= 假内置=假NiladicFunction =假IsComposable =真ParameterTypeSemantics =AllowImplicitConversion架构=DBO> 
<参数名称=LAT1TYPE =真正的模式=在/> 
<参数名称=Long1TYPE =真正的模式=在/> 
<参数名称=LAT2TYPE =真正的模式=在/> 
<参数名称=Long2TYPE =真正的模式=在/> 
< /功能>



我做了一个局部类，并写了这个方法。



 公共部分类GeoDataEntities 
 {
 [EdmFunction（GeoDataModel.Store，DistanceBetween）] 
公共双DistanceBetween（双LAT1，双lon1，双LAT2，双lon2）
 {
抛出新NotImplementedException（）; 
} 
}



我试过很多次使用此代码查询功能但它得到一个错误

  VAR NParcel = db.geoAddresses.Where（G => db.DistanceBetween（21.5,39.5， g.lat，g.lon）小于20）;

当我尝试计数或的foreach 的NParcel我得到这个错误




指定的方法'双DistanceBetween（双人间，双人间人，双人，
双人间）'的类型'EFSample.GeoDataEntities'不能被翻译
到LINQ到实体店的表情。




和堆栈跟踪




在
System.Data.Objects.ELinq.ExpressionConverter.ThrowUnresolvableFunction（表达式$ b在
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FunctionCallTranslator.TranslateFunctionCall $ b表达式）（ExpressionConverter
父母，MethodCallExpression电话，EdmFunctionAttribute
functionAttribute）在
System.Data这.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate（ExpressionConverter
父母，MethodCallExpression LINQ）在
System.Data.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate（ExpressionConverter
父母，BinaryExpression LINQ）在
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression（表达式
LINQ）在
System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda（LambdaExpression
拉姆达，DbExpression输入）在
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate（ExpressionConverter
父母，MethodCallExpression电话，DbExpression&安培;源，
DbExpressionBinding&安培; sourceBinding，DbExpression&安培;拉姆达）留在
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate（ExpressionConverter
父母，MethodCallExpression调用）。 TypedTranslate（ExpressionConverter
父母，MethodCallExpression LINQ）在
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression（表达式
LINQ）在
System.Data.Objects.ELinq.ExpressionConverter .MethodCallTranslator.AggregateTranslator.Translate（ExpressionConverter
父母，MethodCallExpression调用）在
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate（ExpressionConverter
父母，MethodCallExpression LINQ）在
System.Data.Objects.ELinq.ExpressionConverter.Convert（）在
System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan在
（可空 1
forMergeOption） System.Data.Objects.ObjectQuery 1.GetResults（可空 1
forMergeOption）在
System.Data.Objects.ObjectQuery 1.System.Collections.Generic.IEnumerable.GetEnumerator（）
在System.Linq.Enumerable.Single [TSource]（IEnumerable的 1源）在
System.Linq.Queryable .Count之间[TSource]（IQueryable的 1源）



解决方案

这里是你如何做到这一点：



第1步：在EDMX 

 <作用NAME =DistanceBetween集合=false的内置=假NiladicFunction =假IsComposable =假ParameterTypeSemantics =AllowImplicitConversion架构=DBO> 
<&CommandText中GT; 
选择dbo.DistanceBetween（@ LAT1，@ long1，@ LAT2，@ long2）
< /&CommandText中GT; 
<参数名称=LAT1TYPE =真正的模式=在/> 
<参数名称=Long1TYPE =真正的模式=在/> 
<参数名称=LAT2TYPE =真正的模式=在/> 
<参数名称=Long2TYPE =真正的模式=在/> 
< /功能>



第2步：导入功能




  1. 双击 EDMX

  2. 在模型浏览器视图中，展开 GeoDataModel.Store （可能被命名不同）

  3. 展开存储过程/函数

  4. 双击 DistanceBetween

  5. 标量=单

  6. 单击确定



第3步：在C＃中： 

  GeoDataEntities DB =新GeoDataEntities（）; 
 VAR第一= db.DistanceBetween（234，2342，424，243）。首先（）值。





注意是 IsComposable =FALSE并没有的返回类型不要忘记添加：

 <&CommandText中GT; 
选择dbo.DistanceBetween（@ LAT1，@ long1，@ LAT2，@ long2）
< /&CommandText中GT;



希望，帮助....


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)

解决方案

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

这篇关于如何使用标量值函数使用LINQ到实体？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆