调用LINQ查询SQL用户定义的函数 [英] Calling a SQL User-defined function in a LINQ query

问题描述

我有一个很难得到这个工作。我试图做使用上一个IQueryable以下过滤器助手半径搜索。还有一组其它过滤器的RadiusSearch应用之前得到应用。顺序不应该真正的问题，因为我们的目标是要获得查询被推迟到了ToList（）操作。

 公开静态的IQueryable< ApiSearchCommunity> RadiusSearch（这IQueryable的< ApiSearchCommunity>社区）
 {
 VAR centerLatitude = 30.421278; 
 VAR centerLongitude = -97.426261; 
 VAR半径= 25; 
 
返回communities.Select（C =>新建ApiSearchCommunity（）
 {
 CommunityId = c.CommunityId，
市= c.City，
 //距离= c.GetArcDistance（centerLatitude，centerLongitude，c.Latitude，c.Longitude，半径）
}）; 
} 
  

我可以以某种方式写这样GetArcDistance一个帮手上面这反过来又对调用UDF SQL？我试图生成查询是​​以下

  SELECT 
 comms.community_id，
 comms.city ，
 comms.distance 
 FROM（
选择
 c.community_id，
 c.city，
 dbo.udf_ArcDistance（
 30.421278， - 97.426261，
 c.community_latitude，
 c.community_longitude 
）的距离
从社区c）作为通讯科
，其中comms.distance< = 25 
 ORDER BY comms.distance 
  

解决方案

好吧，我想我明白这个问题 - 它的要点是你要能够调用一个SQL UDF为你的LINQ to Entities查询的一部分。

这是如果你使用的数据库或模式第一：

本文介绍了如何做到这一点： http://msdn.microsoft.com/en-us/库/ dd456847（VS.100）的.aspx

要概括起来，首先需要编辑您的EDMX文件中的XML编辑器，在EDMX ：StorageModels >>架构部分，你需要指定一个映射到SQL UDF，如：

 <作用NAME =SampleFunction返回类型=INT模式=DBO> 
<参数名称=参数模式=在类型=整数/> 
< /功能> 
  

然后你需要的地方创建一个静态函数就可以了EdmFunction属性，是这样的：

 公共静态类ModelDefinedFunctions 
 {
 [EdmFunction（TestDBModel.Store，SampleFunction）] 
公共静态INT SampleFunction（INT参数）
 {
抛出新NotSupportedException异常（直接调用不被支持。）; 
} 
} 
  

这方法会映射到UDF在查询时通过实体框架。第一个属性参数是存储空间 - 你可以在架构元素（寻找命名空间）在您的EDMX XML文件中找到。 。第二个参数是UDF的名称

您可以调用它是这样的：

  VAR的结果=从s在context.UDFTests 
选择新的
 {
 =名为testVal ModelDefinedFunctions.SampleFunction（22）
}; 
  

希望这有助于。

I am having a hard time getting this to work. I am trying to do a radius search using the following Filter helper on an IQueryable. There are a set of other filters that get applied before RadiusSearch applies. The order shouldn't really matter since the goal is to get the query to be deferred until a ToList() operation.

public static IQueryable<ApiSearchCommunity> RadiusSearch(this IQueryable<ApiSearchCommunity> communities)
{
    var centerLatitude = 30.421278;
    var centerLongitude = -97.426261;
    var radius = 25;

    return communities.Select(c => new ApiSearchCommunity()
    {
        CommunityId = c.CommunityId,
        City = c.City,
        //Distance = c.GetArcDistance(centerLatitude, centerLongitude, c.Latitude, c.Longitude, radius)
    });
}

Can I somehow write a helper like GetArcDistance above which in turn calls a UDF on SQL? The query I am trying to generate is the following

SELECT 
    comms.community_id, 
    comms.city, 
    comms.distance 
FROM (
    SELECT 
        c.community_id, 
        c.city, 
        dbo.udf_ArcDistance(
            30.421278,-97.426261, 
            c.community_latitude,
            c.community_longitude
        ) AS distance 
    FROM communities c) AS comms 
WHERE comms.distance <= 25 
ORDER BY comms.distance

解决方案

Ok, I think I understand the question - the gist of it is you want to be able to call a SQL UDF as part of your Linq to Entities query.

This is if you're using database or model first:

This article explains how to do it: http://msdn.microsoft.com/en-us/library/dd456847(VS.100).aspx

To sum it up, you first need to edit your edmx file in an xml editor, in the edmx:StorageModels >> Schema section you need to specify a mapping to your sql udf, eg

<Function Name="SampleFunction" ReturnType="int" Schema="dbo">
    <Parameter Name="Param" Mode="In" Type="int" />
</Function>

Then you need to create a static function somewhere with the EdmFunction attribute on it, something like this:

public static class ModelDefinedFunctions
{
    [EdmFunction("TestDBModel.Store", "SampleFunction")]
    public static int SampleFunction(int param)
    {
      throw new NotSupportedException("Direct calls are not supported.");
    }
}

This method will get mapped to the UDF at query time by entity framework. The first attribute argument is the store namespace - you can find this in your edmx xml file on the Schema element (look for Namespace). The second argument is the name of the udf.

You can then call it something like this:

var result = from s in context.UDFTests
            select new
            {
                TestVal = ModelDefinedFunctions.SampleFunction(22)
            };

Hope this helps.

这篇关于调用LINQ查询SQL用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！