Force Entity Framework to optimize/simplify huge queries

13 Jun 2009 1 mins Databases in general, Entity Framework

When you write huge (I mean really huge, not a ~300 lines baby) complex query you may experience, that the generated query looks (well) suboptimal. The reason is that after some line the Entity Framework will gave up on optimization and simply throws it as is. To turn this off and force to simplify it whatever query it is you can add this into your app.config, as Kati Iceva pointed in MSDN forums.

<system.diagnostics>
  <switches>
    <add name="System.Data.EntityClient.IgnoreOptimizationLimit" value="1" />
  </switches>
</system.diagnostics>

But also take into account that this may end up with a significant time spent in EF’s code and at the end the query will be still too big for the store to run it or the store would do better job optimize it. Thus the overall time may be same or even worse (= use carefully).

It’s also worth, if you’re able to write the query a lot simpler, to discuss this in MSDN forums, so the code in EF can be improved.

Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at www.tabsoverspaces.com.