Calling a custom scalar DB function inside a LINQ to Entities query?

Question

Is there a way I can call a custom scalar DB function as part of my LINQ to Entities query? The only thing I can find on the web about this is this page:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/how-to-call-custom-database-functions

However the instructions given here seem to assume you are using a DB-first approach and talk about modifying the .edmx file. What about if you're using a code-first approach? I want to be able to write something like this:

var result = await (
    from itm in _itemDataContext.Items
    where
        itm.QryGroup1 == "Y" &&
        _itemDataContext.Dbo_MyCustomScalarIntFn(itm.QryGroup2) > 0
).ToArrayAsync();

Answer 1

Turns out this functionality is not built in to Entity Framework so I had to use a NuGet package (CodeFirstFunctions) to get it:

---

IItemDataContext

[DbFunction("CodeFirstDatabaseSchema", "fn_IsCorrectProduct")]
bool Fn_IsCorrectProduct(string companyID, string itemCode);

---

ItemDataContext

[DbFunction("CodeFirstDatabaseSchema", "fn_IsCorrectProduct")]
public bool Fn_IsCorrectProduct(string companyID, string itemCode)
{
    // UDF is described in DbFunction attribute; no need to provide an implementation...
    throw new NotSupportedException();
}

Answer 2

I think you can use ExecuteSqlCommand, but you have to hardcode the sql to be executed against BD, like this:

var result = await (
    from itm in _itemDataContext.Items
    where
        itm.QryGroup1 == "Y" &&
        _itemDataContext.Database.ExecuteSqlCommand("Exec yourFunction "+itm.QryGroup2) > 0
).ToArrayAsync();

I dont tested, but should solve your problem.