Using expression trees with EF Core produces strange SQL statements

Question

I am using expression trees to dynamically build my expression at runtime, which is then used in the .Where() clause of IQueryable<T>, as I call my database. The resulting SQL statement looks extremely strange for me and I do not understand what is going on there.

General Information: Project uses Framework 4.7.2, EF Core 3.1.3 NuGet

Example (following is simplified):

Consider a customer Class like this:

public class Customer{
    public string Name {get; set;}
    public int Age {get; set;}
    public string Address {get; set;}
}

The DBContext is set up properly with DBSet<Customer> and so on.

So now I want to use something like ... db.Customer.Where(expression).ToList() ... where I have to build the corresponding expression at runtime. The program will be given the list of search criteria to use for that like List<(string, string)> where the first string will be the name of the property of customer and the second is the value to use for filtering. This way of passing the search criteria can not be changed.

I build my expression tree like this, having converted the list of search criteria to dictionary (Dictionary<string, List<string>>) with property name as key and list of values of actual values to search for. typesDictionary holds the information about property types (name -> string, etc.):

var param = Expression.Parameter(typeof(Customer), "c");
var andList = new List<Expression>();

foreach (var sc in searchCriteria){
    var orList = new List<Expression>();
    foreach (var value in sc.Value{
       var expr = Expression.Equal(
       Expression.Property(param, sc.Key),
       Expression.Constant(Convert.ChangeType(value, typesDictionary[sc.Key]), typesDictionary[sc.Key]));
       orList.Add(expr);
     }
     andList.Add(orList.Aggregate(Expression.Or));
}
var expression = Expression.Lambda<Func<Customer, bool>>(andList.Aggregate(Expression.And), param);

Resulting expression looks like c => ((c.Name == "Bob") OR (c.Name == "John")) AND (c.Age == 12) for example. A bit of an exessive use of parentheses...

Concerning SQL I would expect it to be something like:

SELECT c.Name, c.Age, c.Address
FROM someDB.someSchema.Customers as c
WHERE c.Name = "John" OR c.Name = "Bob" AND c.Age = 13

But what gets created is something like:

SELECT [c].[Name], [c].[Aage], [c].[Address]
      FROM [Customer] AS [c]
      WHERE ((CASE
          WHEN [c].[Age] = CAST(12 AS int) THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END | CASE
          WHEN [c].[Age] = CAST(15 AS int) THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END) | CASE
          WHEN [c].[Age] = CAST(22 AS int) THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END) = CAST(1 AS bit)

What is going on here? Where do all the CASE WHEN and CASTS come from?

Answer 1

Your problem is Expression.Or is bitwise or, so basically | operator. And Expression.And is bitwise and (&). So expression you have is:

c => (c.Name == "John" | c.Name == "Bob" ) & c.Age = 13;

What you want instead (how you would write it by hand) is this:

c => (c.Name == "John" || c.Name == "Bob" ) && c.Age = 13;

And for that you need to use Expression.OrElse and Expression.AndAlso:

foreach (var sc in searchCriteria){
    var orList = new List<Expression>();
    foreach (var value in sc.Value) {
        var expr = Expression.Equal(
            Expression.Property(param, sc.Key),
            Expression.Constant(Convert.ChangeType(value, typesDictionary[sc.Key]), typesDictionary[sc.Key]));
        orList.Add(expr);
    }
    andList.Add(orList.Aggregate(Expression.OrElse));
}
var expression = Expression.Lambda<Func<Customer, bool>>(andList.Aggregate(Expression.AndAlso), param);

After that you should have more "normal" looking sql query generated.