Increase Linq Query Performance by Compiling It

Each time when we fire any Linq to SQL or to any other data-source using Entity Framework query, run-time converts query to expression tree and then into T-SQL statement. So if the query gets fired a number of times in the application, it gets converted in the expression tree to T-SQL statement by run-time. This conversion increases the execution time which in turn effects the performance of the application. To avoid this cost, Microsoft .NET Framework introduces the concept of the complied queries which allows compilation and caching of queries for reuse.

Now, there is a shopping website which lists the products by category, basically it allows filtering of product by the category. So if I have 100 users who logged in to the system and do the filter the product by category, they basically fire...

from p in db.Products where p.Category == category select p

...query to get the result they want. So this will increase the execution cost as this query gets fired a number of times and gets converted in the expression tree and in turn gets the result.

With the help of CompiledQuery class of .NET Framework, I can rewrite my code and it's like below:

public static Func<DataLoadTestDataContext, string, IQueryable<Product>>
  ProductByCategory =
    CompiledQuery.Compile((DataLoadTestDataContext db, string category) =>
     from p in db.Products where p.Category == category select p);

Static Variable

Static variable is used to store, so it's not thread safe and global to all. Because of static variable, compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime. If you don't use the static variable, the query gets complied each time which increases the cost and decreases the performance of the application.

Constraint and Use

Cannot use to store the queries which return Anonymous type, because the anonymous type doesn't have any type to assign generic argument of function.

Useful when query is used heavily and you want to reuse the query, by using this way increase the performance of the application.

Where to Include the Code?

A better place to include the above code is partial class, its extended partial class to the partial class generated by ORM tool.

More: Extend ORM generated class

CodeProject