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?

it may help if you post a code snippet showing how you use it. — Steven Mills, Jan 24 '14 at 08:30
My question is not code-specific. I am asking which alternative to the LINQs `.Contains()` are remaining, if I should avoid it. — ˈvɔlə, Jan 24 '14 at 08:33
I know this is not an alternative to Contains(). There is StartsWith() and EndsWith() methods. — ElectricRouge, Jan 24 '14 at 08:41
Run the generated query in SSMS and then rewrite it in query with joins instead of 'where in'. If and only if the join variation is faster, then try to rewrite your LINQ code into something that produces a query with joins. — sventevit, Jan 24 '14 at 08:42
Other than that all I can think of that is similar is Regex. I dont know if that increases performance. — ElectricRouge, Jan 24 '14 at 08:54
This all really depends on the context in which you are using `Contains`... — James, Jan 24 '14 at 08:57

score 2 · Answer 1 · answered Jan 24 '14 at 09:22

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
)

score 1 · Accepted Answer · edited May 23 '17 at 12:33

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.

Alternative for LINQ's .Contains()

2 Answers2