LinQ to SQL - Compiled Queries

Descrption:

This article demonstrates how to use compiled queries with LinQ. To eliminate the overhead of building query pipeline, compiled queries should be used.

e.g. This is the query to find all customers and employees living in "USA".

db.Customers
  .Select(c => new Person { PersonName = c.ContactName,CountryName= c.Country})
  .Union(db.Employees
  .Select(c => new Person { PersonName = c.FirstName, CountryName = c.Country}))
  .Where(c => c.CountryName == "USA"));

If this query is used frequently, we can avoid creation of query pipeline everytime. To compile the query, we can use CompiledQuery.Compile method. This method takes an argument list as input and a result type. It returns a delegate as a variable that we can use later. So here, we can pass the NorthwindDataContext instance and the country variable in. We will return a IQueryable(of Person) object. So the definition of our compiled query as follows:

public static Func<NorthwindDataContext,string,IQueryable<Person>>
            GetCustomernEmployees =
            CompiledQuery.Compile((NorthwindDataContext db,string country) =>
            db.Customers
                    .Select(c => new Person { PersonName = c.ContactName,CountryName= c.Country})
                    .Union(db.Employees
                    .Select(c => new Person { PersonName = c.FirstName, CountryName = c.Country}))
                    .Where(c => c.CountryName == "USA"));