Calling a SQL User-defined function in a LINQ query

c# entity-framework linq linq-to-entities sql

Question

This is difficult for me to make work. I'm attempting to perform a radius search on an IQueryable using the following Filter helper. Before RadiusSearch is applied, a number of other filters are done. Since the purpose is to have the query delayed until a ToList() transaction, the order shouldn't actually matter.

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

Is it possible for me to create a helper that calls a UDF on SQL, similar to GetArcDistance above? I'm attempting to create the following query.

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
1
13
12/23/2013 7:49:04 PM

Accepted Answer

Okay, I think I understand the question. The main idea is that you want your Linq to Entities query to be able to invoke a SQL UDF.

Specifically, if you use a database or model first:

How to do it is described in this article: http://msdn.microsoft.com/en-us/library/dd456847(VS.100).aspx

To summarize, you must change your edmx file in an xml editor first. You must then specify a mapping to your sql udf under the edmx:StorageModels >> Schema section, for example.

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

Then you must someplace create a static function and add the EdmFunction attribute to it, as in:

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

Entity framework will map this method to the UDF at query time. The store namespace is the first attribute parameter, and you can locate it on the Schema element in your edmx xml file (look for Namespace). The name of the udf is the second argument.

Then you may refer to it as something like this:

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

Hope this was useful.

25
8/23/2016 3:06:22 PM

Popular Answer

ZZZ_tmp


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow