Efficient way to return a List from a List efficiently?

Question

I'm trying to return a List<Item> by feeding a List<Guid> to a repository method as such

public async Task<List<Item>> GetListOfItems(List<Guid> guids)
{
}

I thought about making a GetById call in a foreach loop but I figured this many calls to the DB will just be inefficient.

I also thought about maybe using context.Items.Where(x => x...Contains(guids)) but I don't know how to write it right.

The thing is I'm trying to do this efficiently, hopefully using EF Core but if not possible then using a stored procedure in SQL would do the job.

I'm not advanced enough to figure out the SQL so if it's possible in EF Core that would be awesome.

`context.Items.Where(x => guids.Contains(x.Id)).ToList()`? Obviously, you'll need to replace `Id` with the actual `Guid` property. — 41686d6564 stands w. Palestine, Feb 09 '23 at 02:31
@tymtam it could be as much as 50 max, but per user, so depending on the amount of user running this method it could be a high number — Jimmy, Feb 09 '23 at 02:37

score 0 · Accepted Answer · answered Feb 09 '23 at 02:32

0

You can get all items in one database roundtrip like this:

public async Task<List<Item>> GetListOfItems(List<Guid> guids)
{
  return await context.Items.Where(x => guids.Contains(x.Id)).ToListAsync();
}

answered Feb 09 '23 at 02:32

sa-es-ir

I see, however should it be `x => guids.Contains(x.Id)` or `x => x.Contains(x.guid)` ? – Jimmy Feb 09 '23 at 02:38
Should be this one: ``x => guids.Contains(x.Id)`` – sa-es-ir Feb 09 '23 at 02:48
Awesome, thank you. Could you maybe explain why it is this way ? I'm confused with the syntax because it seems like this is querying the `Id`s against the `guids` list, which maybe it should be but I thought it should query the guids against the Items list. – Jimmy Feb 09 '23 at 02:50
Think about this: you want to search ``x.Id`` in ``guids`` list, so you try to find the database records that is in ``guids`` list or ``guids`` should contains the current ``x.Id``. – sa-es-ir Feb 09 '23 at 02:53
I see, thank you. And is it efficient for a very large amount of requests ? I'm limiting the list of guids to 50 elements, but if 1000 users request that method, would the SQL be efficient ? – Jimmy Feb 09 '23 at 02:59
It depends on how your table created and indexes. But this is a good way for searching in list and will convert to ``IN`` statement in the sql side – sa-es-ir Feb 09 '23 at 03:46

1 Answers1