locked
How to get more than one id in one query? RRS feed

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

    Wednesday, October 30, 2019 9:47 AM

All replies

  • 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

    Wednesday, October 30, 2019 10:25 AM