LINQ expression that will match multiple bulk inputs

Question

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}};

you need to query members who belong to any of listed departments and and of listed divisions? — Robert, Sep 03 '16 at 11:48
`.Select` is an unfiltered selection that allows you to do a transformation - you need to use `.Where` which is a filtered selection without the transformation (which you don't seem to need). — code4life, Sep 03 '16 at 12:44
It is not clear if you want to select matching pairs or if divisions and départements are to be handled independantly. For exemple, if your original data would also contains a line with `6 9` should that line be selected or not? — Phil1970, Sep 03 '16 at 19:46
This is one of many incarnations of [this question](http://stackoverflow.com/q/26198860/861716). Maybe we should mark it as a duplicate. The answer below by Ivan the expression wizard elaborates on my "option 6" there. — Gert Arnold, Sep 03 '16 at 19:50
When you say it does not behave correctly, you should tell us what you get and what you expect as it is not always easy to be sure of both... — Phil1970, Sep 03 '16 at 19:51
When you update your question in a way to "invalidate" some existing answers, you should make it clear which information has changed as it might affect our vote. — Phil1970, Sep 03 '16 at 19:54
@GertArnold Looking at the linked question and your answer there, I definitely agree this is a duplicate. — Ivan Stoev, Sep 04 '16 at 06:59

score 4 · Accepted Answer · answered Sep 03 '16 at 18:50

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

your interpretation of the problem is correct. How efficient is this solution performance wise? — Jasmine, Sep 04 '16 at 06:14
Good question. I think it should be comparable to `Contains` which translates to SQL `IN (...)`. And will have the same issues if the lists are big. You need to try and see. The problem is that looks like you have no other options, even with raw SQL. — Ivan Stoev, Sep 04 '16 at 06:26
But thanks to @Gert Arnold, you may take a look at his answer to [EntityFramework - contains query of composite key](http://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key) which provides more complete analysis of the issue. — Ivan Stoev, Sep 04 '16 at 07:04

score 2 · Answer 2 · answered Sep 03 '16 at 12:33

2

Try with

var member = members.Where(member => divisons.Contains(member.Division) && department.Contains(member.Department)).ToList();

You have used Select clause, I have modiefied to work with Where.

answered Sep 03 '16 at 12:33

Robert

3,353
4
32
50

that was a typo, it should be .Where – Jasmine Sep 03 '16 at 16:13
@Jasmine, is it working now? – Robert Sep 03 '16 at 16:54
What is the difference from OP query (except `ToList` instead of `ToArray`? – Phil1970 Sep 03 '16 at 19:48
@Phil1970 OP modified question. OP user Select clause in original question – Robert Sep 03 '16 at 19:49

Slai · Answer 3 · 2016-09-04T11:31:36.497

0

This is similar to @Robert 's answer, but will filter the results to make sure the division and department are the same "pair":

int[] divisons = { 4, 6 }, departments = { 9, 3 };
var members = new[] { new { Division = 1, Department = 2 } }; // just for testing

var query = members.Where(m => divisons.Contains(m.Division) 
                         && departments.Contains(m.Department));

var member = query.AsEnumerable().Where(m => Array.IndexOf(divisons, m.Division) 
                        == Array.IndexOf(departments, m.Department));

edited Sep 04 '16 at 11:31

answered Sep 03 '16 at 20:08

Slai

22,144
5
45
53

See the discussion between Renan Araújo and me. The same applies to your answer. And more, because you can't use `Enumerable.Range` in an EF LINQ query. – Gert Arnold Sep 03 '16 at 20:23
@GertArnold thanks. Will using `join` or Robert's answer load all members into memory too? – Slai Sep 03 '16 at 20:31
Well,it's not about loading data into memory, EF (6) doesn't do that automatically. The query just won't run. Robert's answer (Contains) will translate alright into a SQL query with `IN` clauses, but the result isn't correct because it will also return other combinations, like a record Division 4, Department 3 (if present). Two separate joins will display the same flaw. – Gert Arnold Sep 03 '16 at 20:39
@GertArnold, in the comments of his question, OP stated he wants "to query members which belongs to any of listed departments and listed divisions". – Robert Sep 04 '16 at 08:08
Well, it's an answer "yes" to a question they probably interpret differently. The problem is in confining the query to specific combinations, otherwise Ivan's answer wouldn't have been accepted. – Gert Arnold Sep 04 '16 at 08:18

LINQ expression that will match multiple bulk inputs

3 Answers3

Linked