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

How about this: https://stackoverflow.com/questions/29517627/entity-framework-6-code-first-custom-functions — George Vovos, Mar 04 '18 at 19:56
Wow. I'm surprised it's not built into the framework especially since it seems to be built in for DB-first. — Jez, Mar 04 '18 at 20:10
This my help too https://weblogs.asp.net/Dixin/EntityFramework.Functions — George Vovos, Mar 04 '18 at 20:23
Well, some db first (edmx) features have not been implemented in code first. That's why 3rd party libraries are trying to fill that gap. — Ivan Stoev, Mar 05 '18 at 11:45

score 3 · Accepted Answer · answered Mar 06 '18 at 09:20

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

score 1 · Answer 2 · answered Mar 05 '18 at 14:58

1

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.

answered Mar 05 '18 at 14:58

Rui Estreito

262
1
10

Unfortunately this doesn't work; I get the error `LINQ to Entities does not recognize the method 'Int32 ExecuteSqlCommand(System.String, System.Object[])' method, and this method cannot be translated into a store expression.`. – Jez Mar 05 '18 at 15:46

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

2 Answers2