在LINQ查询中调用SQL用户定义的函数

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.

我很难让这个工作。我正在尝试使用IQueryable上的以下Filter助手进行半径搜索。在RadiusSearch应用之前，还有一组其他过滤器可以应用。顺序不应该真正重要，因为目标是将查询推迟到ToList（）操作。

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

我可以以某种方式编写一个像GetArcDistance这样的帮助程序，然后在SQL上调用UDF吗？我想要生成的查询如下

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

2 个解决方案

#1

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.

好吧，我想我理解这个问题 - 它的要点是你希望能够将SQL UDF作为Linq to Entities查询的一部分。

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

本文将介绍如何执行此操作：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

总而言之，首先需要在xml编辑器中编辑edmx文件，在edmx：StorageModels >> Schema部分中，您需要指定到sql udf的映射，例如

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

然后你需要在其上创建一个带有EdmFunction属性的静态函数，如下所示：

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.

此方法将在实体框架的查询时映射到UDF。第一个属性参数是商店命名空间 - 您可以在Schema元素的edmx xml文件中找到它（查找Namespace）。第二个参数是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.

希望这可以帮助。

#2

if you use Code-First approach, then you cannot call UDFs as you want (as of EF6) - here is the proof, and another one. You are only limited to calling UDF as a part of your SQL query:

如果您使用Code-First方法，那么您不能按照自己的意愿调用UDF（从EF6开始） - 这是证明，另一个是证明。您只能将UDF作为SQL查询的一部分来调用：

bool result = FooContext.CreateQuery<bool>(
    "SELECT VALUE FooModel.Store.UserDefinedFunction(@someParameter) FROM {1}",
    new ObjectParameter("someParameter", someParameter)
).First();

which is ugly IMO and error-prone.

这是丑陋的IMO，容易出错。

Also - this MSDN page says:

此外 - 这个MSDN页面说：

The process for calling a custom function requires three basic steps:

调用自定义函数的过程需要三个基本步骤：

Define a function in your conceptual model or declare a function in your storage model.

在概念模型中定义函数或在存储模型中声明函数。

which essentially means you need to use Model-First approach to call UDFs.

这实际上意味着您需要使用Model-First方法来调用UDF。

#1