The LINQ expression could not be translated and will be evaluated

.net-core .net-core-3.0 c# entity-framework-core

Question

My code:

public static IQueryable<ServicesData> Query(DbContext dbc, string path1 = null, string path2 = null, string path3 = null) =>
    dbc.ServicesData.Where(sd => SQLmatch(sd.Path1, path1) && SQLmatch(sd.Path2, path2) && SQLmatch(sd.Path3, path3));

static bool SQLmatch(string match, string pattern) {
    if (match is null) match = "";
    if (pattern is null) pattern = "";
    bool wildcard = pattern.Contains("%") || pattern.Contains("_");
    return wildcard ? EF.Functions.Like(match, pattern) : object.Equals(match, pattern);
}

using (var dbc = new DbContext()) {
    var q = Query(dbc, "User", "%").ToList();

Using latest EFcore 3.0 this fails with:

Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where ((SQLmatch([sd].Path1, __path1_0) AndAlso SQLmatch([sd].Path2, __path2_1)) AndAlso SQLmatch([sd].Path3, __path3_2))' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

I would appreciate any help with this.

EDIT

It seems the problem is with the function call, if I put the code in SQLmatch into a single expression like this:

public static IQueryable<ServicesData> Query(DbContext dbc, string path1 = null, string path2 = null, string path3 = null) =>
    dbc.ServicesData.Where(sd => 
    ((path1 ?? "").Contains("%") || (path1 ?? "").Contains("_") ? EF.Functions.Like((sd.Path1 ?? ""), (path1 ?? "")) : object.Equals((sd.Path1 ?? ""), (path1 ?? "")))
    && ((path2 ?? "").Contains("%") || (path2 ?? "").Contains("_") ? EF.Functions.Like((sd.Path2 ?? ""), (path2 ?? "")) : object.Equals((sd.Path2 ?? ""), (path2 ?? "")))
    && ((path3 ?? "").Contains("%") || (path3 ?? "").Contains("_") ? EF.Functions.Like((sd.Path3 ?? ""), (path3 ?? "")) : object.Equals((sd.Path3 ?? ""), (path3 ?? ""))));

it works !

Why can't EFCore process the code in a separate function ?

1
1
5/3/2019 2:46:25 AM

Accepted Answer

In this case, the query will be converted to SQL and run in the database server and you can only use those methods in these kinds of queries which are supported by LINQ and can be directly converted to DB functions. So you cannot use your custom functions in these queries, even not all the inbuilt .net methods are supported for LINQ to entities. You can refer to this link for supported functions. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/supported-and-unsupported-linq-methods-linq-to-entities

3
5/3/2019 4:46:15 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow