Entity Framework Core: User Defined SQL Functions

Question

Is it possible to invoke a user-defined SQL function from the query interface in EF Core? For example, the generated SQL would look like

select * from X where dbo.fnCheckThis(X.a, X.B) = 1

In my case, this clause is in addition to other Query() method calls so FromSQL() is not an option.

Answer 1

I just managed this with help from this article (H/T @IvanStoev for his comment on the question).

In your DbContext class:

[DbFunction("my_user_function_name")]
public static bool SatisfiesMyUserFunction(int i)
{
    throw new Exception(); // this code doesn't get executed; the call is passed through to the database function
}

Note that the function must be in the DbContext class, even though it is static.

Then create a database migration and define the user function in the script.

Usage:

var query = db.Foos.Where(f => MyDbContext.SatisfiesMyUserFunction(f.FieldValue));