LINQ to SQL Compiled Queries
As LINQ nears release, people are starting to consider the performance implications that the extra overhead brings. Currently there are two threads on this: thread1, thread2. For those that are intested in the performance implications, I highly recommend checking out the outstanding series of posts by Rico Mariani.
Ultimately if you want to get the best performance, you need to use the Compile function of the CompiledQuery class. Let's consider the following query (Note, turn this into C# by adding a semi-colon at the end if necessary):
From p In context.Products() _
Where (p.Price >= minimumPrice) _
Select p)
In this query, we are searching for the products that are "Expensive" in that their price exceeds a price value that we set. If we regularly consume this, we can eliminate the overhead of building the query pipeline. See the Matt Warren talk about this pipeline on the deep dive video at on Charlie Calvert's blog for more information regarding the overhead that is necessary to evaluate a query.
To compile the query, we can leverage the CompiledQuery.Compile method. This method takes an argument list as input and a result type. It returns a delegate as a variable that we will be able to consume. Thus in this case, we can pass the DataContext instance and the minimumPrice variable in. We will return a IQueryable(of Product) object. thus we can start the definition of our compiled query as follows:
VB:
CompiledQuery.Compile( _
Function(context As MyDataContext, minimumPrice As Decimal) _
From p In context.Products() _
Where (p.Price >= minimumPrice) _
Select p)
C#:
CompiledQuery.Compile(
(MyDataContext context, decimal minimumPrice) =>
from p in context.Products
where p.Price >= minimumPrice
select p);
In this case we are defining our query as a Lambda function which the Compile can convert to the corresponding expression tree representation. If you are unfamiliar with Lambdas in VB, check out Timothy Ng's recent MSDN Magazine article.
With the delegate functions declared, all that is left is to actually assign them to a variable that we can consume later. To do this, we define a static/shared function which returns the Func anonymous delegate type. By defining it as a static/shared function, the compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime. Notice, we are defining the signature of the query, not the results. We are free to change the parameter without needing to re-compile the query's structure. Here's the completed function calls in VB and C#:
Public Shared ExpensiveProducts As Func(Of MyDataContext, Decimal, IQueryable(Of Product)) = _
CompiledQuery.Compile(Function(context As MyDataContext, minimumPrice As Decimal) _
From p In context.Products() _
Where (p.Price >= minimumPrice) _
Select p)
public static Func<MyDataContext, decimal, IQueryable<Product>>
ExpensiveProducts =
CompiledQuery.Compile((MyDataContext context, decimal minimumPrice) =>
from p in context.Products
where p.Price >= minimumPrice
select p);
The syntax for using the compiled query takes a bit of thinking to get your head around. We are essentially creating a function which returns a function rather than returning a value. If you're familiar with functional programming such as OCaml and F#, this concept should be easy to grok. For the rest of us, piece out the individual method components as laid out above and you should be able to understand it after a while ;-)
To consume our new function, we simply instantiate a DataContext instance and pass it along with the minimumPrice we want to set as our minimum value.
Dim context As New MyContext(My.Settings.MyConnectionString)
Dim Items = ExpensiveProducts(context, minimumPrice)