How to get more than one id in one query?

Question

User1655654435 posted

Hi, I want to fetch tables from a list of ids (ints), but i can't seem to write a query that is optimal for this.

My method takes a list of ints.

I could just fetch all the tables if that type, iterate over them and return all the corresponding tables with the id list. But this is not a performant solution when you have many talbes.

iif i do this, it does not work because only the last query runs:

List<int> ids;
var query = _context.Customers.AsNoTracking().Where(a => a.Age > 55);

foreach(var item in ids)
{
   query = query.Where(i => i.ID == item);
}

return query.ToList();

the only way i have found is to just count the ids. then do a if else statement where if it is 2 numbers, the query is Where(i => i.ID == ids[0] | i.ID == ids[1]);

But that is a horrible way, because it's not dynamic (i need to set a maxumum on the list to correspont to the number of if statements)

Answer 1

User753101303 posted

Hi,

The trick is to remember that you are writing C# code (even if translated to SQL). so you can use :

 query = query.Where(i => MyListOfIds.Contains(i.ID)); // check if the list contains a value, translated to value IN (list of values) on the SQL side