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.
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));
DbContext, I didn't need to make it static. That means I could do this instead: var query = db.Foos.Where(f => db.SatisfiesMyUserFunction(f.FieldValue));