Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calling a SQL User-defined function in a LINQ query

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
like image 792
Praveen Avatar asked Nov 21 '13 20:11

Praveen


People also ask

How do you call a user-defined function in Entity Framework?

EF Core allows for using user-defined SQL functions in queries. To do that, the functions need to be mapped to a CLR method during model configuration. When translating the LINQ query to SQL, the user-defined function is called instead of the CLR function it has been mapped to.

Can we call a user-defined function in a select statement?

User-defined functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur. For example, user-defined functions can be used in the following: The select list of a SELECT statement.

How do you call a table valued function in Entity Framework?

Step 1 − Select the Console Application from the middle pane and enter TableValuedFunctionDemo in the name field. Step 2 − In Server explorer right-click on your database. Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.


2 Answers

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.

like image 113
Matt Whetton Avatar answered Oct 16 '22 11:10

Matt Whetton


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:

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.

Also - this MSDN page says:

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

  1. 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.

like image 40
avs099 Avatar answered Oct 16 '22 12:10

avs099