Using custom sql server function in linq?

Question

I have a table in SQL Server that holds all store information.

Columns are:

storeId, locationLongitude, locationLatitude

and a sql function getDistance which takes parameters of (Customer Longitude, Customer Latitude, store Longitude, store latitude)

My current sql query is:

SELECT TOP 5 
   dbo.[getDistance] (473.510432, -122.154381, locLatitude, locLongitude, 'Miles') AS distance, 
   loclatitude, loclongitude, storeId , 
FROM 
   storelocation WITH(NOLOCK)  
ORDER BY 
   distance;

I'm currently caching all the store information then running a linq to filter out the data is there any way to call getDistance in linq?

Steve Cooper · Accepted Answer · 2014-08-23 19:03:21Z

You can use this package to extend Linq-to-Entities (EF 6.1) so that your context supports table-valued functions.

First, you need to add a method to your context, like so; (swap the name "MyContext" for your context type name)

    [DbFunction("MyContext", "getDistance")]
    [DbFunctionDetailsAttribute(ResultColumnName = "locationId", DatabaseSchema = "dbo")]
    public IQueryable<StoreInfo> getDistance(int locLatitude, int locLongitude)
    {
        return F2<StoreInfo, int, int>("getDistance", "locLatitude", locLatitude, "locLongitude", locLongitude);
    }

This calls a utility function you'll also need to add;

    private IQueryable<TResult> F2<TResult, TParam1, TParam2>(string functionName, string parameterName1,
        TParam1 parameterValue1, string parameterName2,
        TParam2 parameterValue2)
    {
        var queryString = string.Format("[{0}].[{1}](@{2}, @{3})", GetType().Name, functionName, parameterName1, parameterName2);
        var parameter1 = new ObjectParameter(parameterName1, parameterValue1);
        var parameter2 = new ObjectParameter(parameterName2, parameterValue2);
        var query = this.ObjectContext.CreateQuery<TResult>(queryString, parameter1, parameter2);
        return query;
    }

Lastly, in OnModelCreating, register the type returned by your function;

        modelBuilder.ComplexType<StoreInfo>();

Now your context has an IQueryable which you can use in Linq-to-entities.

Collectives™ on Stack Overflow

Using custom sql server function in linq?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related