Alternative for LINQ's .Contains()

Question

I was looking for some tips to improve my entity framework query performance and came accross this useful article.

The author of this article mentioned following:

08 Avoid using Contains

In LINQ, we use contains method for checking existence. It is converted to "WHERE IN" in SQL which cause performance degrades.

Which faster alternatives are remaining for me?

Solution 1

It is very context dependent, what you should be looking at is not avoiding .Contains() but rather how do you avoid WHERE xx IN yy in SQL. Could you do a join instead? Is it possible to specify an interval rather than discrete values?

A perfect example is presented here: Avoid SQL WHERE NOT IN Clause

Where it was possible to avoid it by using a join.

I would say that WHERE xx IN yy is usually just a half a solution, often what you really want is something else and you only get halfway there instead of going there directly, like in the case of a join.

Solution 2

Contains is perfectly valid for the scenarios you WANT WHERE IN

EG:

var q = from p in products where new[]{1,50,77}.Contains(p.productId) select p;

gets (essentially) converted to

SELECT * FROM products WHERE ProductId IN (1,50,77)

However if you are checking for existence I would advice you to use .Any() , which gets converted to EXISTS -

EG

var q = from p in products
           where p.productsLinkGroups.Any(x => x.GroupID == 5)
           select p

Gets (more or less) coverted to:

SELECT * FROM products p 
WHERE EXISTS(
  SELECT NULL FROM productsLinkGroups  plg
  WHERE plg.GroupId = 5 AND plg.ProductId = p.ProductId
)