Jasmine Jasmine - 3 months ago 11
C# Question

LINQ expression that will match multiple bulk inputs

How can I write a LINQ expression that will match multiple bulk inputs?

This is my database table

Members
Division, Department
1 3
4 9 *
5 1
6 3 *
9 2


I want to select the members marked with a *

Here are my inputs

int[] divisions = new int[2] {4, 6};
int[] department = new int[2] {9, 3};


Here's the query I tried

var selectedMembers = members.Where(member => divisions.Contains(member.Division) && department.Contains(member.Department)).ToArray();


This does not behave correctly in EF. It works when with a single input but not both inputs (Division and Department)
It should return pairs matching in the inputs
eg:

Member[] selectedMembers = new Member[2] { new Member{Division=4,Department=9}, new Member{Division=6,Department=3}};

Answer

This type of filtering is not supported directly by EF.

One possible solution is to build dynamically a predicate like this:

member => (member.Division == divisions[0] && member.Department == departments[0])
       || (member.Division == divisions[1] && member.Department == departments[1])
       ...
       || (member.Division == divisions[N-1] && member.Department == departments[N-1]);

Here is how you can do that:

var parameter = Expression.Parameter(typeof(Member), "member");
var predicate = Expression.Lambda<Func<Member, bool>>(
    Enumerable.Range(0, divisions.Length)
    .Select(i => new Expression[]
    {
        Expression.Equal(Expression.Property(parameter, "Division"), Expression.Constant(divisions[i])),
        Expression.Equal(Expression.Property(parameter, "Department"), Expression.Constant(departments[i])),
    }
    .Aggregate(Expression.AndAlso))
    .Aggregate(Expression.OrElse),
    parameter);
var query = members.Where(predicate);
Comments