0

I have a table that contains objects from type person, each person has a unique column of type string called id, and I have a hashset of Id's that I want to fetch the person's object for.

I thought I could do it efficiently by doing this:

HashSet<string> ids = //Some HashSet of ids

var idsHashSet = DbContext.Persons.Select(p => p.Id).ToHashSet();
idsHashSet.IntersectWith(ids);
var result = DbContext.Persons.Where(p => idsHashSet.Contains(p.Id)).ToList());

I saw in the database itself that the query being sent is:

SELECT <something> 
FROM <something> 
WHERE p.name IN ('id1', 'id2', id3'...)

And it's not efficient because I already know that each element in the HashSet is present in the database, so I wanted to know, is there a more efficient way to do it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dr.Work
  • 1

2 Answers2

1

You can do this to shorten your code:

var result = DbContext.Persons
    .Where(x => ids.Contains(x.Id))
    .ToList();

But it will be converted to the exact same SQL query, though, which is efficient enough.

Ivan Gechev
  • 706
  • 3
  • 9
  • 21
0

"And it's not efficient because I already know that each element in the HashSet is present in the database" I'm not sure what you mean by this....the db has no idea if all the ids are present or not, regardless of whether you do.

How large is the set of ids? Safer to create a temp table with the IDs and return a JOIN select if it's very large...otherwise I Don't see an issue with your query as it is.

barbrady
  • 321
  • 3
  • 13