I was fiddling with compiled queries yesterday and thought I would share what I saw as results. This is anything but laboratory benchmark testing so take it for what it’s worth. I did only a very simple query to start with, finding SalesOrders whose total is greater than a given number.
You can do this with Entity SQL or LINQ to Entities. Here is my LINQ to Entities compiled query:
Dim compQuery = CompiledQuery.Compile(Of AdventureWorksLTEntities, Decimal, IQueryable(Of SalesOrderHeader))( _ Function(ctx As AdventureWorksLTEntities, total As Decimal) _ From order In ctx.SalesOrderHeader _Where (order.TotalDue >= total) _ Select order)
To interpret this, Compile’s signature looks like this:
Compile(list of args, returntype)(delegate)
It takes a list of arguments and a return type then performs an operation (defined in the delegate) on the arguments. I’m defiining this compilation to recieve an AdventureWorksLTEntities instance and a decimal and the return will be an IQueryable of SalesOrderHeaders.
VB
CompiledQuery.Compile(Of AdventureWorksLTEntities, Decimal, _ IQueryable(Of SalesOrderHeader))
C#
CompiledQuery.Compile<AdventureWorksLTEntities, Decimal, IQueryable<SalesOrderHeader>>For the delegate, I use a labmda epxression that says what to do with the parameters which is to build the query.
VB
(Function(ctx As AdventureWorksLTEntities, total As Decimal) _
From order In ctx.SalesOrderHeader _
Where (order.TotalDue >= total) _
Select order)c#
(ctx, total) =>
from order in ctx.SalesOrderHeader
where (order.TotalDue >= total)
select order)
The whole query is tied to a variable
Dim myCompiledQuery=CompiledQuery.Compile(....
Then when I want to run the query, I invoke it and pass in the parameters
Dim AWEntities As New AdventureWorksLTEntities
Dim orderTotal=200
Dim orders As ObjectQuery(Of SalesOrderHeader) = compQuery.Invoke(AWEntities, orderTotal)
The first time the compiled query is run, it still has to compile, but after that it uses the results of the compilation and can swap in the parameters without needing to recreate the generated command tree.
You can see a big difference between running the query without pre-compilation and running it with the compiled query.
Brian Dawson does some more intense testing and compares LINQ to Entities to Entity SQL as well in this blog post. But I just needed to actually do it myself, rather than only reading about it.
LINQ to SQL also has compiled queries. Rico Mariani has a series of posts on this starting here.