LINQ Contains() alternatives

Question

I have a list of int and I need to select all record from my query where the id is contained in the second list as showed below:

     //my list of ids
    var ids=[myquery].select(x=> x.id)

    query = query.Where(x => ids.Contains(x.Id));

Now LINQ will convert the above in :

SELECT *
FROM [MyTable] 
WHERE ([x].[id] IN (108,687, 689, 691, 694, 705, 703,.....)

Now the ids list will grow a lot and I guess this will ruin the performances.

What would be a better solution considering the the ids list will contain more than 200K item ?

probably you should use a navigation property, but it depends what `[myquery]` and `query` are. — Dave Cousineau, Dec 15 '19 at 12:38
You need to send those 200K items some way. Linq converting to such IN (...) is fine. I had the same need, with a slight difference where my ID list was available as a comma separated list (string). I ended up creating a CLR function (MS SQL Server). — Cetin Basoz, Dec 15 '19 at 12:57

score 1 · Answer 1 · answered Dec 15 '19 at 12:42

It depends on your model, but you should probably use a navigation property.

Given that you currently have something like this:

var ids =
   context
   .Entity1
   .Where(x => x.Property == value)
   .Select(x => x.ID)
   .ToHashSet();

var items =
   context
   .Entity2
   .Where(x => ids.Contains(x.ID))
   .ToList();

write something like this instead:

var items =
   context
   .Entity2
   .Where(x => x.Entity1.Property == value)
   .ToList();

You may need to add such an association to your model before this will be possible.

StepUp · Answer 2 · 2019-12-15T12:46:52.437

I would suggest to create a stored procedure as:

there is no unnecessary calls between C# and database(e.g., you are collecting these 200K ids)
less code in C#. So your code will be cleaner and clearer
performance is better as sometimes EF generates inefficient SQL code

So calling stored procedure would look like this:

var user = "johndoe";

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

OR try to use NOT IN operator if items are less 200K:

query = query.Where(x => !ids.Contains(x.Id));

SQL:

SELECT *
FROM [MyTable] 
WHERE ([x].[id] NOT IN (108,687, 689, 691, 694, 705, 703,.....)

LINQ Contains() alternatives

2 Answers2