score:1

Accepted answer

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.

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

score:1 

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

Related Query

More Query from same tag