.NET – Writing compiled queries for LINQ to SQL
When writing database scripts, we prefer stored procedures over regular Sql statements for a couple of reasons. Stored procedures are precompiled, so they avoid the query compile time; on a network, passing a short string to invoke a stored procedure reduces a huge network traffic if the stored procedure had number of lines of code, and if we were using Sql statements instead of stored procedures. Other benefits are there too.
When using ORMs like LINQ to SQL, or ADO.NET Entity Framework where the Sql queries are dynamically created for you on the fly, those Sql statements have to be compiled by the Sql server every time a query is issued. And before the query is issued to the SQL server, LINQ has to parse the expression tree and spew out the SQL, which happens almost every time. In order to reduce the process of walking through the expression tree and constructing a SQL statements every time, they could be cached. To enable caching of a query, the ORMs offer a feature called compiled queries, so when we have a situation where we iterate over a query over and over again, it could be compiled and kept to improve performance.
Let’s take a look at how easy it is to write them and how to make them globally available. [The recent version Entity Framework 5.0 released with .Net framework 4.5 has compiled queries feature on by default – http://stackoverflow.com/questions/7337027/ef-4-2-impact-of-auto-compiled-linq-queries]
Below code shows you how you would normally define a DataContext and query a table.
If we wanted this query to be parsed at compile time and translated to SQL statements, so LINQ to SQL or Entity Framework does not have to dynamically build the SQL query every time a query like this is executed, then we should make it a compiled query to improve performance.
The Compile method of the CompiledQuery class accepts a Func delegate with template parameters and returns a Func delegate. At the basis, what we always should know about the Compile method is, we can pass in up to 15 input parameters, and take one output parameter. If you see the query above, we are using the NorthwindDataContext variable named nwc, that we are using in our LINQ query. And the entire LINQ query returns an IQueryable of Anonymous type for us.
Below is a sample of making the same query available in a compiled form local to a method. We call the Compile method with a NorthwindDataContext as a parameter and the entire anonymous delegate (the method inside the compile method) returns an IQueryable of the an Anomynous type derived from the customer.
When we want to call the compiled query, we pass in a datacontext nwc and the rest of the code is same.
This is good, most developers tend to have all the SQL queries in one cs file and refer it from there. If you follow that approach for compiled queries, and make then static instead, then the queries would be compiled once during the static variable initialization, and stays compiled untill the application’s lifetime yielding a significant boost in performance.
To demonstrate that, I am going to slightly modify the LINQ query to accept one more string paramter named country. Now the regular LINQ query we have looks like below
Nothing’s changed, except that we are passing a new string parameter named country along with the NorthwindDataContext. Remember when I said the entire result of the LINQ query is an IQueryable of the returned type. In this case the type that is returned is Customer, so it is IQueryable<Customer>. And the input parameters are NorthwindDataContext and a string. If that is clear, then what gets actually returned from the CompiledQuery.Compile method is a Func delegate. And that Func delegate in our case here, is going to accept two input parameters – one the NorthwindDataContext object, another a string; it’s output is IQueryable<Customer>.
When it was local, we used a var keyword and the type was automatically inferred to us. Now when we are talking about making a compiled query as a static member variable of a class, we don’t really have an option to use the var keyword, but to specify the exact type.
So, how would you define a Func delgate that takes NorthwindDataContext and string as input parameters, and returns an IQueryable<Customer> as an output parameter?
Func<NorthwindDataContext, string, IQueryable<Customer>>
Create the above static variable in a class named CompiledQueries, then you can access it, just like we did before, but we gain performance.
Download the complete source code – https://renouncedthoughts.files.wordpress.com/2013/02/linqtosqlcompiledqueries1.doc
The word document contains embedded zip file with the original Visual Studio solution.