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.