Today a colleague told me that he got a strange Entity Framework error

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

I immediately ask him what kind of huge and big query he was issuing to EF, but the answer is pretty simple.

Context.EntityName.Where(u => listOfId.Any(s => s.Equals(u.UserId))).ToList()

and listOfId is a List<Int32> with 100 integer

Basically he want to retrieve all entities that are related to a list of UserIds, having the list of UserId inside a simple list of integer. Even if the query looks good, this got translated to a monster Sql Query. Here it is a screenshot of the query (it is really too big to include in source)

image

Actually this probably happens because EF is not hable to handle well an Any operator applied to a list of integer and he treats it as any other ANY operator, so he creates basically a subquery for each of the number in the list.

The solution is quite obvious, rewrite the query

Context.EntityName.Where(u => listOfId.Contains(u.UserId)).ToList()

This is a much simpler query, it simply express that we want all the entities with a UserId property that is contained in the list of Id. EF is now capable of translating in a much simpler SQL.

image

This query is what we expect, a simple query with an IN operator.

The lesson learned from this example is: Try to express your query in the simplest way and always look at what EF generates, to avoid bad surprise.

Gian Maria

Tags: ,

2 Responses to “Entity Framework error Some part of your SQL statement is nested too deeply”

  1. Hi Gian, just watch out with the Contains method, since it can easily generate a SQL query that os larger thatn the maximum allowed batch size (see http://msdn.microsoft.com/en-u.....43432.aspx).

    I’ve been bitten by that one before :-) The only solution other than rewriting the thing is to batch the queries in smaller parts

    /Jakob

  2. @Jackob you are right, query was already batched in batch of 100 :), greater list size usually breaks the query.