Using Expressions to build Array.Contains for Entity Framework

user_286479 0
Viewed 684

I want to have a variable field in both Where-Contains and Select. "field_a" is the guy I want to be variable (sometimes I want field_b or _c; they are strings). The code below properly builds the Select as Select(x => x.field_a). How do I get the second part of the Where clause to say, && targetCodes.Contains(x.field_a)? [db is a DbContext, itemsArray is an array of objects that have a string property called Code.]

    using (var db = dbFactory.CreateInstance())
    {
        var parameter = Expression.Parameter(typeof(myTable), "x");
        var field = Expression.Property(parameter, "field_a");
        var selector = Expression.Lambda(field, parameter);
        var targetCodes = itemsArray.Select(i => i.Code).ToArray();
        var query = db.myTables
            .Where(x => x.companyId == 1 && targetCodes.Contains(x.field_a))
            .Select((Expression<Func<myTable, string>>)selector);
        return await query.ToArrayAsync();
    }
expression-trees c#
2 Answers

Probably the most difficult part is to find MethodInfo of the .Contains() method. You could use typeof(IEnumerable<string>).GetMethod(...).Where(...), but it is usually difficult to do it right for generic method with several overloads. This is a small trick that employs C# compiler to find the correct overload for you by creating temporary expression:

Expression<Func<IEnumerable<string>, bool>> containsExpr = (IEnumerable<string> q) => q.Contains((string)null);
var containsMethod = (containsExpr.Body as MethodCallExpression).Method;
// containsMethod should resolve to this overload:
// System.Linq.Enumerable.Contains<string>(IEnumerable<string>, string)

The rest of the program just builds expression by calling appropriate Expression.XYZ() methods:

var companyIdEquals1 = Expression.Equal(
    Expression.Property(parameter, nameof(myTable.companyId)),
    Expression.Constant(1));

var targetCodesContains = Expression.Call(
    containsMethod,
    Expression.Constant(targetCodes),
    field/*reuses expression you already have*/);

var andExpr = Expression.And(companyIdEquals1, targetCodesContains);
var whereExpr = (Expression<Func<myTable, bool>>)Expression.Lambda(andExpr, parameter);

var query = db//.myTables
    .Where(whereExpr)
    .Select((Expression<Func<myTable, string>>)selector);
user_12866700

There are several ways to do that. In this particular case you don't even need to deal with expressions because you could use simply chain Where after the Select (chained Where conditions are combined with && in the final query):

var query = db.myTables
    .Where(x => x.companyId == 1)
    .Select((Expression<Func<myTable, string>>)selector)
    .Where(v => targetCodes.Contains(v));

But to answer your question how to build the expression representing targetCodes.Contains({field}), since the actual call (w/o the extension method sugar) you need is Enumerable.Contains<string>(targetCodes, {field}), the simplest is to use the following Expression.Call method overload specifically provided for "calling" static (generic and non generic) methods:

public static MethodCallExpression Call(
    Type type,
    string methodName,
    Type[] typeArguments,
    params Expression[] arguments
);

In your case it could be used like this:

var containsCall = Expression.Call(
    typeof(Enumerable), nameof(Enumerable.Contains), new [] { typeof(string) },
    Expression.Constant(targetCodes), field);
user_52025630
Related