Linq2Entities CompiledQuery for a query that uses joins
I have a query that doesn't work too well for example. the generated SQL is suboptimal.
The original statement looked something like this: (/)>
ctx.Table1.Where(t => ...)
.OrderBy(t => ....)
.Select(t => new {Table1 = t,
SomeProperty = t.Table2.SomeProperty,
SomeProperty2 = t.Table2.SomeProperty2,
AnotherProperty = t.Table3.AnotherProperty,
...
}
I looked in SQL Profiler and found that the generated SQL would be joining the same table multiple times and the statement would take about 1 second to execute.
Then I rewrote the expression to the following:
from t in ctx.Table1
join t2 in ctx.Table2 on t.key equals t2.key into lt2
from t2 in lt2.DefaultIfEmpty()
join t3 in ctx.Table3 on t.key equals t3.key into lt3
from t3 in lt3.DefaultIfEmpty()
where t ...
orderby t...
select new {Table1 = t, .... }
This created a much nicer statement that, when captured from the SQL profiler and executed in control studio, is twice as fast as the statement generated by the code in the previous example.
However, when you run the code in the second example, the time it takes to create the EF expression is far greater than the time it takes to optimize the query.
So how do I go about writing operator number two like CompiledQuery
. I don't know at all how to return an anonymous type from CompiledQuery
.
source to share
The workaround I found for using CompiledQueries is:
- Add a private InitQueryX () method before every QueryX () method that LINQ to Entity uses.
- Use attributes and reflection to call all InitQueryX () methods from the Init () method.
- The Init () method is called once at application startup.
This forces the queries to be compiled at startup, but allows you to write queries in a more flexible way than CompiledQueries does.
InitQueryX () must use multiple dummy inputs so that it spans all paths in the QueryX () method (for example, to cover unit test code).
Whenever possible, the inputs to InitQueryX () should be mocks that result in 0 rows in the database, so the Init () method takes less time to run.
source to share