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

问题描述

我很难让它发挥作用.我正在尝试使用 IQueryable 上的以下过滤器助手进行半径搜索.在 RadiusSearch 应用之前，还应用了一组其他过滤器.顺序应该无关紧要，因为目标是将查询推迟到 ToList() 操作.

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)
    });
}

我能否以某种方式编写一个像上面的 GetArcDistance 这样的帮助程序，然后在 SQL 上调用 UDF?我试图生成的查询如下

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

推荐答案

好吧，我想我明白这个问题了 - 其要点是您希望能够调用 SQL UDF 作为您的 Linq to Entities 查询的一部分.

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:

这篇文章解释了如何做:http:///msdn.microsoft.com/en-us/library/dd456847(VS.100).aspx

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

总结一下，你首先需要在xml编辑器中编辑你的edmx文件，在edmx:StorageModels >> Schema部分你需要指定一个映射到你的sql udf，例如

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>

然后您需要在某处创建一个带有 EdmFunction 属性的静态函数，如下所示:

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.");
    }
}

此方法将在查询时由实体框架映射到 UDF.第一个属性参数是商店命名空间 - 您可以在 edmx xml 文件中的 Schema 元素(查找命名空间)中找到它.第二个参数是 udf 的名称.

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)
            };

希望这会有所帮助.

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