LINQ to SQL CompiledQuery

In the earlier posts on LINQ to SQL performance optimization, I discussed about the LINQ Best Practices and LINQ DataLoadOptions. This discussion will remain incomplete if I don't include CompiledQuery.Compile.

While developing the application we often come across scenario where we are required to execute a similar nature of query many times.

When we execute a query using LINQ there are number of steps involved before final SQL query is generated, and each time we call the method to fetch data whole process starting from query generation to its execution is carried out. In this life cycle the overhead that is easily visible is that we are every time generating the query.

LINQ's CompiledQuery.Compile is used to avoid this extra overhead. If you have an application executing structurally similar query, CompiledQuery.Compile is one thing that can help you increase the performance of your application.

CompiledQuery.Compile compiles the query once and execute it for the rest of the time with different set of parameters.

// Method in which whole process of query generation and execution take place every time

private static void CompiledQueryMethod(string orderID)

{

SampleDataContextDataContext db = new SampleDataContextDataContext();

var result = from c in db.Customers

join o in db.Orders on c.CustomerID equals o.CustomerID

where o.OrderID == orderID

select new { c };

}

In this case whole process of query generation to execution is carried out each time CompiledQuery method is called.

Now lets do the same using CompiledQuery.Compile way.

// This will compile the query and return a delegate that can be used to execute for different

// set of parameters

public static Func<SampleDataContextDataContext, int, IQueryable<Customer>>

GenerateCompiledQuery = CompiledQuery.Compile((SampleDataContextDataContext db, int orderID) =>

from c in db.Customers

join o in db.Orders on c.CustomerID equals o.CustomerID

where o.OrderID == orderID

select c);

The above method returns a delegate that can be cached and used several times just by changing the input parameter.

private static void CompiledQueryMethod(int orderID)

{

SampleDataContextDataContext db = new SampleDataContextDataContext();

var result = GenerateCompiledQuery(db,orderID);

}

Comments

Israel OcbinaPosted on: 9/1/2012 11:17:31 AM

Very nice and simple sample coding.. It help me clear my mind from preventing using linq to sql but now i'm encourage to use it cause of the CompiledQuery method.. Thanks!

Add Comment

Comment

Name
(Required)

E-mail (Privacy assured)
(Required)

Subscribe to this site by email

Test Your Skill

LINQ to SQL CompiledQuery

Add Comment

Poll of the Day

Related Contents

Online Test Papers

Related Tutorials

Fun @ Work

Top Blogs