Entity Framework – Contains() on empty collection

Recently when I was doing a performance analysis I noticed an interesting query and I need to admit it was first time when I saw it.

My stackify profiler presented me the following SQL query:

The structure of this query can vary depending on what additional statements you use in your query (i.e. include, select) but they key part presented below always exists in every scenario.

FROM ( SELECT 1 AS X ) AS [SingleRowTable1]

It appeared that such SQL query is generated when you make a contains on an empty collection, in short your LINQ query looks like below:

To improve it you just need to verify whether your collection that you use to filter data has any elements. If has then make a query, if not then it doesn’t make any sense to execute a query in this scenario.


UPDATE (01/12/2017 – referring to Marek comment)
It’s tough to say whether you should introduce described change but based on my experience and scenarios I had to solve you should know about the following potential problems:

  • This kind of query is compiled whenever executed. When you have a few include statements, the query compilation phase can take even up to 500ms. You can read more about it here http://devmate.net/2017/10/entity-framework-the-query-plan-cache-story/
  • Additional roundtrip is made to a database – take a look at you network latency when communication is made to a database.
  • Database processing – when your DB is not busy then query execution time is very low, but when you have a very high load in a database, then execution time is much higher for such “dummy” query.

3 Replies to “Entity Framework – Contains() on empty collection”

  1. Pingback: dotnetomaniak.pl
  2. In my opinion for normal systems 1ms is not worth for adding this check. Let be honest – database will solve this instantly – but thanks for info

Leave a Reply

Your email address will not be published. Required fields are marked *