Increase LINQ to ENTITY performance using Compiled Query

If you are having an application that executes structurally similar queries many times in the Entity Framework, you can increase the performance by compiling the query once and executing it several times with different parameters.

For example if you are building a web application which is going to get thousands of hit per hour by your end-users. There is a runtime cost associated with each LINQ queries that you write because on every single hit, your LINQ to Entity queries need to be parsed,converted to SQL statements to get your work done. This can consume much of your CPU time, making your site slow. In this scenario you can compile the query once and reuse that with different parameters.

In .NET framework 4, System.Data.Linq namespace provides the CompiledQuery Class which compiles and caches the queries for reuse. This class contains Compile method with several overloads. This method creates a new delegate to represent the compiled query. The query compiles once during only the first execution.
E.G. :-

Suppose In an medical application you need to get a patients total no of visits. Here the patientId is the parameter which changes from patient to patient.
So the query to get its visit count is

public int GetVisitCount(long patientId)
{
     using(DataContext contextObject = new DataContext(ConnectionString))
     {
          int visitCount = (from pv in contextObject.PatientVisit
                               where pv.FK_PATIENT_ID == patientId
                               select pv.VISIT_ID).Count();
          return visitCount;
     }
}

In the above case each time the function is called, the linq Query is parsed and compiled and returns the visit count. But using Linq Compiled query,

// Create the delegate using Compiled query
public static int compiledQuery =
 
    CompiledQuery.Compile((DataContext contextObject, long vistId) =>
    (from pv in contextObject.PatientVisit pv.FK_PATIENT_ID == patientId select pv.VISIT_ID).Count());
      
//Invoke the delegate using different parameter
public static int GetVisitCount(long patientId)
{
     using(DataContext contextObject = new DataContext(ConnectionString))
     {
          int visitCount = compiledQuery.Invoke(contextObject, patientId);
          return visitCount;
     }
}

The reason to declare the compiledQuery as static is to compile it only once and reuse it later by all threads. If you don’t declare compiledQuery as static, then you don’t get the performance gain because compiling queries everytime is even worse than regular Linq queries.

It can also be helpful in situations where you are calling a particular function for several times from other functions in that page. Hence to avoid the same query getting executed for several times, it is better to compile that once and just invoke the delegate with desired parameter. For the very first time Compiled Query may take similar time to return the result as compared to regular function call. But on subsequent calls it takes much lesser time as it uses the result of compilation without recreating the command tree.