Wednesday, September 16, 2015

LINQ: Problem with Deferred execution

This morning I started my day googling for an exception in the code:
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries
And as most cases, I ended up with a Stackover solution:

The code that I was cleaning up was as bellow:

This was breaking for large data but working fine for small data.

It was breaking when the Except() is used to compare large data – the reason is EF most cases does Deferred execution- so all the incompleteCourseId, completedCourseId and requeriedCourseId are evaluated when the Except() is called. For small data it can execute – but for large data it breaks.

Similar thing can happen when complex query needs to be executed through LINQ; So possibly its a good idea to split the queries into multiple ones and evaluate whenever required/whenever requires fetching large data.

So the solution is I forcefully evaluated each of the query using ToList() and it seems it is working good now. Off course cost is multiple SQL call.