EF.Functions.Like in a compiled query could not be translated to SQL equivalent

Question

I am writing a compiled query which takes a DBContext and search string as input and returns a list of Class1 It basically runs 3 filters on the class1 objects and returns a filtered list of class1 objects.

Main query:

var temp = await context.Class1.Where(c => EF.Functions.Like(c.param1, $"%{query}%") && c.param2== 1 && c.param3!= 1).OrderBy(p => EF.Functions.Like(p.param1, $"{query}%") ? 0 : 1).AsNoTracking().ToListAsync();

This actually filters the list and then sorts them in an order where the names starting with the search query are shown before the others. Then

var result = temp.Select(a => new Class2{ T1= a.A1, T2= a.A2, T3= a.A3}).Distinct().Take(10).ToList();

Till now this is the Compiled Query I have wrote:

private static Func<DbContext, string, IEnumerable<Class1>> Search =
            EF.CompileQuery((DbContext context, string query) =>
            context.Class1
            .Where(c => EF.Functions.Like(c.param1, $"%{query}%") 
             && c.param2== 1 
             && c.param3!= 1)
            );

But when this compiled query is called it throws this exception: the linq expression could not be translated

How to solve this?

Update: This is solved now.

private static Func<DbContext, string, IEnumerable<Class2>> Search =
            EF.CompileQuery((DbContextcontext, string query) =>
            context.Class1
            .Where(c => c.param1.ToLower().Contains(query) && c.param2== 1 && c.param3!= 1)
            .OrderBy(p => p.param1.ToLower().StartsWith(query) ? 0 : 1)
            .Select(a => new Class2{ T1 = a.A1, T2 = a.A2, T3 = a.A3 })
            .Take(10).AsNoTracking());

What version of EF are you using? Can you use c.param1.Contains(query) ? — Klamsi, May 19 '21 at 08:40
am using EF core. I can use c.param1.Contains(query), but then it is case sensitive as well as am not sure if that is much faster in run time — Sayansen, May 19 '21 at 09:02
see this: https://stackoverflow.com/questions/45708715/entity-framework-ef-functions-like-vs-string-contains/45709434 — Sayansen, May 19 '21 at 09:04
If you use the Contains, can you use the StringComparison parameter? There you can define case sensivity. — Klamsi, May 19 '21 at 09:06
Look at the full exception message. It tells you what's wrong, which will give a hint how to fix it. — Gert Arnold, May 19 '21 at 09:11
@Klamsi No i tried using String.contains(query, StringComparison param), then also the same excep: the linq expression could not be translated — Sayansen, May 19 '21 at 09:48

score 1 · Answer 1 · answered May 19 '21 at 11:32

private static Func<DbContext, string, IEnumerable<Class2>> Search =
            EF.CompileQuery((DbContextcontext, string query) =>
            context.Class1
            .Where(c => c.param1.ToLower().Contains(query) && c.param2== 1 && c.param3!= 1)
            .OrderBy(p => p.param1.ToLower().StartsWith(query) ? 0 : 1)
            .Select(a => new Class2{ T1 = a.A1, T2 = a.A2, T3 = a.A3 })
            .Take(10).AsNoTracking());

score 1 · Accepted Answer · answered May 19 '21 at 11:39

You seems to be hitting yet another EF Core limitation. The problem is not the EF.Functions.Like method, but the usage of the interpolated string inside, which as you found works inside regular query, but not inside compiled query definition.

The solution/workaround is to use string concatenation in place of string interpolation:

private static Func<DbContext, string, IEnumerable<Class1>> Search =
    EF.CompileQuery((DbContext context, string query) => context.Class1
    .Where(c => EF.Functions.Like(c.param1, "%" + query + "%") // <-- 
     && c.param2== 1 
     && c.param3!= 1)
    );

The resulting SQL query is a bit different, but at least you got a translation.

Okay. So EF core compiled query does not allow usage of the interpolated string with $"{string}" but allows string concat? this was a big learning for me. Thanks! — Sayansen, May 19 '21 at 12:05

osman Rahimi · Answer 3 · 2021-05-19T09:03:36.937

0

If you are using EF Core, you don't need using CompileQuery, just use Ef.Function.Like as below

    var users = context.Users.Where(a => EF.Functions.Like(a.FirstName, "%a%")).ToList();

So Rewriting your code in this way would be like this:

    var result= context.Class1
        .Where(c => EF.Functions.Like(c.param1, $"%{query}%")
         && c.param2 == 1
         && c.param3 != 1).ToList();

Updated: If you want to get its result as t-sql, you can use ToQueryString() like this:

  var users = context.Users.Where(a => EF.Functions.Like(a.FirstName, "%a%")).ToQueryString();

edited May 19 '21 at 09:03

answered May 19 '21 at 08:47

osman Rahimi

1,427
1
11
26

What actions are you going to do after getting its compiled? – osman Rahimi May 19 '21 at 09:01
oh you mean yeah since compiled query is getting compiled into sql anyway beforehand why would i need EF.Functions.Like to separatelt convert it into sql. Right? i got your point. thanks – Sayansen May 19 '21 at 09:06
2

@osmanRahimi The question is about compiled queries, not about whether or not to use them nor about alternatives. Also, `"%a%"` does nothing with the range variable `a`. – Gert Arnold May 19 '21 at 11:17

EF.Functions.Like in a compiled query could not be translated to SQL equivalent

3 Answers3