Expression predicate with parameter on mandatory string

Question

I'm creating a predicate dynamically for a LINQ-to-SQL query. This predicate is based on a string property (NOM). If I make this predicate with Expression.Constant(value), the SQL generated for the predicate is as follows:

("Extent1".NOM LIKE '%A%')

If I do it with a parameter (so that my query cached in the database is not dependent on the value of the predicate) with the following code (partial code):

Expression<Func<string>> closure = () => value;
var rightExp = Expression.Convert(closure.Body, member.Type);
Expression body = Expression.Equal(member, rightExp);

I get the following predicate in SQL :

(("Extent1".NOM LIKE :p__linq__0) OR (("Extent1".NOM IS NULL) AND (:p__linq__0 IS NULL)))

Is it possible to obtain the same predicate as in my first example?

EDIT 1 :

Here is the code after following @abolfazlsadeghi comment :

Expression<Func<TEntity, bool>> GetLambdaWithParameterOnNom2<TEntity>(string value)
{
    var objType = typeof(TEntity);
    var parameter = Expression.Parameter(objType, "x");

    var member = Expression.Property(parameter, objType, "Nom");

    Expression<Func<string>> closure = () => "%" + value + "%";
    var rightExp = Expression.Convert(closure.Body, member.Type);
    Expression body = Expression.Equal(member, rightExp);
    
    var method = value.GetType().GetMethod("Contains");
    var call = Expression.Call(member, method, member);

    return Expression.Lambda<Func<TEntity, bool>>(body, parameter);
}

But SQL request was always:

(("Extent1".NOM LIKE '%' || :p__linq__0 || '%') OR (("Extent1".NOM IS NULL) AND ('%' || :p__linq__0 || '%' IS NULL)))

you must use StartsWith to generate ("Extent1".NOM LIKE '%A%') — abolfazl sadeghi, Jun 23 '23 at 13:39
@abolfazlsadeghi should not it be `Contains` instead of `StartsWith`? — Guru Stron, Jun 23 '23 at 14:20
@GuruStron,I am ashamed. I saw wrong. I thought it was like this ''A%'' ,you must use Contains to generate — abolfazl sadeghi, Jun 23 '23 at 14:24
@Olof, you can use this code: https://stackoverflow.com/a/36548204/10193401 or share whole code and example to I fix it — abolfazl sadeghi, Jun 23 '23 at 14:27
The problem is in the hand-built expression. It doesn't just use `Contains`, it also uses `Equals` which *must* take into account NULL semantics. Why are you building LINQ expressions like this in the first place? Why not write `myEntities.Where(x=>x.NOM.Contains('banana"))` ? — Panagiotis Kanavos, Jun 26 '23 at 08:01

score 0 · Accepted Answer · answered Jun 26 '23 at 12:09

Thank you @panagiotis-kanavos, here is the right code :

Expression<Func<TEntity, bool>> GetLambdaWithParameterOnNom3<TEntity>(string value)
{
    var objType = typeof(TEntity);
    var parameter = Expression.Parameter(objType, "x");

    var member = Expression.Property(parameter, objType, "Nom");

    Expression<Func<string>> closure = () => value;
    var rightExp = Expression.Convert(closure.Body, member.Type);

    var method = value.GetType().GetMethod("Contains");
    var call = Expression.Call(member, method, rightExp);

    return Expression.Lambda<Func<TEntity, bool>>(call, parameter);
}

Expression predicate with parameter on mandatory string

1 Answers1