Thursday, June 4, 2009

LINQ to SQL - Compiled Query



LINQ for data is supported in two basic ways.

You can use LINQ to DataSets, which uses extension methods to make table data and rows and fields accessible in LINQ queries. No preparation work is necessary—this is pure ADO.Net coding.

The other way to use LINQ with data is to use LINQ to SQL. LINQ to SQL requires that you create an ORM.


LINQ to SQL ORM can be generated in four ways –


-          SQLMetal – Entity Class Generator

-          LINQ to SQL Class designer 

-          Custom Code

-          LINQ to Entity - ADO.NET Entity Framework – Out of scope for this post.


Scope of Work –


-          Compiled Queries






Compiled Query –


-          Compiled queries offer opportunity to optimize query performance.

-          In many applications you might have code that repeatedly executes the same query, possibly with different argument values.

-          By default, LINQ to SQL parses the language expression each time to build the corresponding SQL statement, regardless of whether that expression has been seen previously.

-          Compiled queries allow LINQ to SQL to Avoid Reparsing The Expression And Regenerating the SQL statement for each repeated query.



The below code snippet shows an example of a simple compiled query, executed twice with varying parameters.



DataContext db = new DataContext();

var customers = 
CompiledQuery.Compile((DataContext context, string filterCountry )  =>             

            from c in db.Customers
             where c.Orders.Count > 5
             select c;

foreach(var row in 
customers(db, "USA")
foreach(var row in 
customers(db, "Spain"))











Thanks & Regards,

Arun Manglick || Senior Tech Lead


No comments:

Post a Comment