Alternative for LINQ's .Contains()

c# entity-framework linq performance sql

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?

1
1
3/14/2018 2:02:53 PM

Accepted Answer

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.

1
5/23/2017 12:33:44 PM

Popular Answer

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
)


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow