Entity Framework 6 Code First Custom Functions

c# entity-framework entity-framework-6 linq

Question

I'm attempting something like this:

How can scalar-valued functions be used with linq to entities?

However, I only use DbContext and write the code first; I don't use EDMX.

I discovered this:

https://codefirstfunctions.codeplex.com/

However, the usage is inappropriate. To be able to do the following is what I am attempting to do:

var locations = context.Locations.Where(e => Functions.LatLongDistanceCalc(e.Lat, e.Long, lat, long) >= 10)

Where it will invoke the LatLongDistanceCalc scalar function on SQL Server.

Is it possible to carry this out without using EDMX? I'm aware that you can create a manual query, but doing so wouldn't be ideal since I want to retrieve entities using lazy loading proxies and other advanced features in addition to creating a more complicated query.

1
17
5/23/2017 12:10:47 PM

Accepted Answer

A scalar SQL function should be usable in yourWhere criterion between zzz-8 and zzz

Suppose you wish to map the [dbo] SQL function. According to [LatLongDistanceCalc] and the set of tests:

public class MyDataContext: DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       //...

       modelBuilder.Conventions.Add(new FunctionsConvention("dbo", this.GetType()));
    }

    // "CodeFirstDatabaseSchema" is a convention mandatory schema name
    // "LatLongDistanceCalc" is the name of your function

    [DbFunction("CodeFirstDatabaseSchema", "LatLongDistanceCalc")]
    public static int LatLongDistanceCalc(int fromLat, int fromLong,
                                                       int toLat, int toLong)
    {
       // no need to provide an implementation
       throw new NotSupportedException();
    }
}

then, usage would be:

context.Locations
       .Where(e => MyDataContext.LatLongDistanceCalc(e.Lat, e.Long, lat, long) >= 10)
30
5/27/2016 2:07:06 PM


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