Retrieve records from the database matching multiple values of a list

Question

I have a problem with writing a C# linq query for retrieving data from the database, based on multiple columns of a filter list.

The list of items contains multiple columns (For example A and B) and is dynamic. My first idea was to write an any statement in an where statement, but this is not allowed in EF.

var result = _repository.Where(x => items.Any(y => x.A == y.A && x.B == y.B));

I also tried the filter first only on A, retrieve all data and filter on B, but that did not perform well.

var ListA = items.Select(x => x.A).ToList();
var result = _repository.Get(x => ListA.Contains(x.A));

An other way would be to create some c# code to generate something like this:

SELECT A,B,C,D
FROM Items 
WHERE 
    (A = 1 AND b = 1) OR 
    (A = 7 AND b = 2) OR 
    (A = 4 AND b = 3)

But there is no decent way to do this.

Has anyone an idea how to fix this issue?

Answer 1

So, not entirely in Linq but one way to do this would be using a Predicate / PredicateBuilder (info on there here)

This would allow you to set up something like

var predicate = PredicateBuilder.False<YourType>();
foreach (var item in items)
{
    var innerpred = PredicateBuilder.True<YourType>();
    innerpred = innerpred.And(x=> x.A == item.A);
    innerpred = innerpred.And(x=> x.B == item.B);
    predicate = predicate.Or(innerpred);
}

then your conditional would be

var result = _repository.Where(predicate);

you can easily move the predicate generation into a static method or anything like that to clean up the code, but it would cause the where clause to generate SQL of

WHERE 
(A = 1 AND b = 1) OR 
(A = 7 AND b = 2) OR 
(A = 4 AND b = 3)

which is what you are after, obviously the initial loop through may be slow depending how many items you have, though if the SQL table is indexed correctly it should still be a fast query