There is no doubt that tehre are some drawbacks to Linq to Sql. One of them is that the Sql statement is built dynamically so it is needed to be parsed and compiled each time you run it. Fortunately .Net 3.5 has a solution for this problem. System.Data.Linq namespace includes a class named CompiledQuery which is responsible for caching the compiled version of a Linq to Sql query. This class has a static method called Compile which takes a Func<T,S,R> delegate. In this signature, T is the type of a DataContext (i.e. HRMDataContext) , S is the type of a predicate to filter the query and R is the type of returned result. Needless to say that it must be IQueryable<T>.
In this article we will see how to pre-compile a query, its limitations and how it really improves the speed of a Linq query.
To pre-compile a query we must define a public static field of type Func<T,S,R> . What we assign to this field is the result of CompiledQuery.Compile method:
public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult …
In the above line, testDataContex is the type of a DataContext inside the project, SearchCriteria is type of a class or struct that is designed for passing search criteria to .Compile method. For example, suppose that in testDataContext, we have a Table named Person. We have also defined a class (or struct) named SearchCriteria as bellow:
public class SearchCriteria
{
public int id { set; get; }
public string FirstName { set; get; }
public string LastName { set; get; }
}
Now to get these definitions to work with a precompiled query we can write such a statement:
public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult =
System.Data.Linq.CompiledQuery.Compile(
(testDataContext dc , SearchCriteria criteria ) =>
from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p
);
That’s it. At this point, FilteredResult contains a pre-compiled query and can be used this way:
testDataContext dc = new testDataContext();
SearchCriteria criteria = new SearchCriteria();
criteria.id = -1;
criteria.FirstName = “Bill”;
criteria.LastName = “Gates”;
List<Person> p = FilteredResult(dc, criteria).ToList();
The above code creates instances of testDataContext (dc) and SearchCriteria (criteria) and passes them to FilteredResult as arguments. The result of FilteredResult is IQueryable<Person> we have called .ToList() extension method to get a List<Person> series.
One upsetting point about pre-compiled queries is that you can not use a stored-procedure to make a compiled query. In the above Linq to Sql code, if you write “from C in usp_GetPerson() …” you will get an error indicating that stored procedures are not allowed to be used.
Now let’s see how much precompilation can be helpful. I have written a small Console application that runs two version (one is compiled and one is not) of a query over a database for 1000 times. The time needed to run each query is as follows:
Compiled query takes 0 minutes, 1 seconds and 62 milliseconds.
Regular query takes 0 minutes, 13 seconds and 328 milliseconds.
As it is clear, the compiled query is greatly faster than a regular query. Notice that in a Linq model, nothing will really happen unless we iterate over the result of the query. Therefore, I have written a foreach statement to iterate over the result of queries. I also have written a small query at the beginning of the program to make Linq manager open a connection to Sql Server. If we do not do this, the compiled query will surprisingly takes longer!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
namespace CompiledQuery
{
class Program
{
public static Func<testDataContext , SearchCriteria, IQueryable<Person>> FilteredResult =
System.Data.Linq.CompiledQuery.Compile(
(testDataContext dc , SearchCriteria criteria ) =>
from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p
);
static void Main(string[] args)
{
testDataContext dc = new testDataContext();
SearchCriteria criteria = new SearchCriteria();
IQueryable<Person> Q = null;
// The following code makes Linq manager to open a connection to Sql Server
var init = from p in dc.Persons select p;
foreach (Person person in init) ;
criteria.id = -1;
criteria.FirstName = “Bill”;
criteria.LastName = “Gates”;
DateTime BeginTime = DateTime.Now;
for (int i = 0; i < 1000; i++)
{
Q = FilteredResult(dc, criteria);
foreach (Person person in Q) ;
}
DateTime EndTime = DateTime.Now;
TimeSpan Diff1 = EndTime – BeginTime;
BeginTime = DateTime.Now;
for (int i = 0; i < 1000; i++)
{
Q = from p in dc.Persons
where (p.id == criteria.id || criteria.id == -1)
&& (p.FirstName == criteria.FirstName || criteria.FirstName == string.Empty)
&& (p.LastName == criteria.LastName || criteria.LastName == string.Empty)
select p;
foreach (Person person in Q) ;
}
EndTime = DateTime.Now;
TimeSpan Diff2 = EndTime – BeginTime;
Console.WriteLine(”Compiled query takes : {0}:{1}:{2}”, Diff1.Minutes, Diff1.Seconds, Diff1.Milliseconds);
Console.WriteLine(”Regular query takes {0}:{1}:{2}”, Diff2.Minutes, Diff2.Seconds, Diff2.Milliseconds);
Console.ReadKey();
}
}
}
You can download the source of of a full sample project from here.