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?

I think you might be looking for a `Join`. https://stackoverflow.com/questions/21051612/entity-framework-join-3-tables — MikeH, Nov 01 '18 at 15:57
Create a stored procedure and call that if you want performance. My last project was designed with a hybrid system of sprocs and ef calls depending on the speed/need of the operations. — ΩmegaMan, Nov 01 '18 at 17:14
So you can do this using predicates in C# - performance would depend on how many things there are in Items - will post a full answer when I get home if no one else has — Gibbon, Nov 01 '18 at 17:53
Using the PredicateBuilder was the best solution for my situation, because a join was not allowed, because my list had no primitive types. Keep in mind that is important to include the AsExpandable() statement. — R Pelzer, Nov 02 '18 at 11:13

score 4 · Accepted Answer · edited Nov 02 '18 at 11:16

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

Thank you for the answer and that article is invaluable! :) – Ash K Nov 24 '21 at 17:51 — Ash K, Nov 24 '21 at 17:51

Retrieve records from the database matching multiple values of a list

1 Answers1