Entity Framework 6 Code First Custom Functions

Question

I'm trying something similar to this:

How to use scalar-valued function with linq to entity?

However I'm not using EDMX, but instead just DbContext and code first.

I've come across this:

https://codefirstfunctions.codeplex.com/

But the usage isn't suitable. What I am trying to achieve is to be able to do this:

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

Where it will call a scalar function (LatLongDistanceCalc) on SQL Server.

Is there any way to do this without using EDMX? I know that you can construct a manual query but this wouldn't be prefereable because I want to bring back entities with lazy loading proxies etc as well as building up a more complex query.

skeletank · Accepted Answer · 2016-05-27 14:07:06Z

37

You should be able to use a scalar SQL function in your Where criterias with CodeFirstStoreFunctions

Assuming you want to map SQL function [dbo].[LatLongDistanceCalc], and according to the test suite:

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

usage would then be:

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

edited May 27, 2016 at 14:07

skeletank

2,9165 gold badges44 silver badges81 bronze badges

answered Apr 9, 2015 at 13:00

jbl

15.5k3 gold badges37 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AndrewC Over a year ago

I'm not sure how I didn't get this earlier but this works 100%. Thanks you!

Richard Housham Over a year ago

I would like to add that this works if you are within the select/context. If you are outside of it - it won't work.

dotnetspark Over a year ago

I've been trying to use it with a Max function I've created, no input parameters and so far no success. I always get the NotSupportedException.

jbl Over a year ago

@yopez83 you should post a question with details on your problem so that someone can help you

Net205 Over a year ago

I have a question, this answer don't support this situation. sql like this: select id, dbo.fnDecrypt(seller, 'key123') as seller from table where dbo.fnDecrypt(seller, 'key123') like '%seller name%', and the first parameter of fnDecrypt is varbinary(max), it's result using sql server EncryptByPassPhrase algorithm，seller property of class is string.

Collectives™ on Stack Overflow

Entity Framework 6 Code First Custom Functions

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related