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

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

问题描述


可能重复：

可以将SQL级别的功能提供给LINQ to Entity查询？


我有一个标量函数来获取两点之间的距离，我想使用它来查询最接近的记录。
标量函数适用于linq到sql，但失败与EF



标量函数

  USE [GeoData] 
 GO 
 
 / ******对象：UserDefinedFunction [dbo]。[DistanceBetween]脚本日期：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; 
 / *中级结果a。 * / 
 DECLARE @a as float（53）; 
 SET @a = SQUARE（SIN（@dLatitude / 2.0））+ COS（@ dLat1InRad）
 * COS（@ dLat2InRad）
 * SQUARE（SIN（@dLongitude / 2.0））; 
 / *中间结果c（Radians中的大圆距离）。 * / 
 DECLARE @c为真实; 
 SET @c = 2.0 * ATN2（SQRT（@a），SQRT（1.0  -  @a））; 
 DECLARE @kEarthRadius为真实的; 
 / * SET kEarthRadius = 3956.0英里* / 
 SET @kEarthRadius = 6376.5; / * kms * / 
 
 DECLARE @dDistance为真实; 
 SET @dDistance = @kEarthRadius * @c; 
 return（@dDistance）; 
 END 
 
 GO

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



$ b pre> < Function Name =DistanceBetween之间的距离ReturnType =realAggregate = falseBuiltIn =falseNiladicFunction =falseIsComposable =trueParameterTypeSemantics =AllowImplicitConversionSchema =dbo>
< Parameter Name =Lat1Type =realMode =In/>
< Parameter Name =Long1Type =realMode =In/>
< Parameter Name =Lat2Type =realMode =In/>
< Parameter Name =Long2Type =realMode =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）;

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


指定的方法'Double DistanceBetween（Double，Double ，Double，
Double）'在类型'EFSample.GeoDataEntities'不能被翻译成
到LINQ to Entities存储表达式。


和stacktrace


at
System.Data.Objects.ELinq.ExpressionConverter.ThrowUnresolvableFunction（Expression
$ b System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FunctionCallTranslator.TranslateFunctionCall（ExpressionConverter
父，MethodCallExpression调用，EdmFunctionAttribute
functionAttribute）在
System.Data .Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate（ExpressionConverter
parent，MethodCallExpression linq）at
System.Data.Objects.ELinq.Express在
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression（Expression
linq）at
System.Data.Objects中的ionConverter.BinaryTranslator.TypedTranslate（ExpressionConverter
parent，BinaryExpression linq）
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate（ExpressionConverter
parent，MethodCallExpression调用，DbExpression& amp;来源，
DbExpressionBinding& sourceBinding，DbExpression& lambda）
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate（ExpressionConverter
父，MethodCallExpression调用）在
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator。
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression（Expression
linq）
System.Data.Objects.ELinq.ExpressionConverter（ExpressionConverter
parent，MethodCallExpression linq）在
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate（ExpressionConverter
parent，MethodCallExpression linq）$ b $中的方法CallTranslator.AggregateTranslator.Translate（ExpressionConverter
父，MethodCallExpression调用） b System.Data.Objects.ELinq.ExpressionConverter.Convert（）at
System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan（Nullable 1
forMergeOption）at
System.Data.Objects.ObjectQuery
System.Data.Objects.ObjectQuery 1.CetResults（Nullable 1
forMergeOption）1.System.Collections.Generic.IEnumerable.GetEnumerator（）
在System.Linq.Enumerable.Single [TSource]（IEnumerable 1源）
System.Linq.Queryable.Count [TSource]（IQueryable 1来源）



解决方案

这是你如何做到的：



步骤1：在edmx 

 < Function Name = DistanceBetweenAggregate =falseBuiltIn =falseNiladicFunction =falseIsComposable =falseParameterTypeSemantics =AllowImplicitConversionSchema =dbo> 
< CommandText> 
选择dbo.DistanceBetween（@ lat1，@ long1，@ lat2，@ long2）
< / CommandText> 
< Parameter Name =Lat1Type =realMode =In/> 
< Parameter Name =Long1Type =realMode =In/> 
< Parameter Name =Lat2Type =realMode =In/> 
< Parameter Name =Long2Type =realMode =In/> 
< / Function>

步骤2：导入功能


  1. 双击 edmx

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

  3. 展开存储过程/函数

  4. 双击 DistanceBetween

  5. Scalars = Single

  6. 单击确定

步骤3：在C＃中： / strong> 

  GeoDataEntities db = new GeoDataEntities（）; 
 var first = db.DistanceBetween（234，2342，424，243）.First（）。





注意 code> IsComposable =false而没有 ReturnType ，不要忘记添加：

 < CommandText> 
选择dbo.DistanceBetween（@ lat1，@ long1，@ lat2，@ long2）
< / CommandText>

希望帮助....


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天全站免登陆