Entity Framework error Some part of your SQL statement is nested too deeply

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)


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.


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