C# 3.0 Cookbook, 3rd Edition

1.3. Reuse Parameterized Queries with LINQ to SQL

Problem

You need to execute the same parameterized query multiple times with different parameter values, but you want to avoid the overhead of parsing the query expression tree to build the parameterized SQL each time the query executes.

Solution

Use the CompiledQuery.Compile method to build an expression tree that will not have to be parsed each time the query is executed with new parameters:

	var GetEmployees =
	        CompiledQuery.Compile((Northwind db, string ac, string ttl) =>
	                   from employee in db.Employees 
	                   where employee.HomePhone.Contains(ac) &&
	                         employee.Title == ttl 
	                   select employee);

	Northwind dataContext = new Northwind(Settings.Default.NorthwindConnectionString);

The first time the query executes is when it actually compiles (where GetEmployees is called the first time in the foreach loop). Every other iteration in this loop and in the next loop use the compiled version, avoiding the expression tree parsing:

	foreach (var employee in GetEmployees(dataContext, "(206)", "Sales Representative"))
	{
	    Console.WriteLine("{0} {1}", 
	        employee.FirstName, employee.LastName); 
	}       

	foreach (var employee in GetEmployees(dataContext, "(71)", "Sales Manager"))
	{
	    Console.WriteLine("{0} {1}", 
	        employee.FirstName, employee.LastName);
	}

Discussion

We used var for the query declaration, as it was cleaner, but what var actually is in this case is:

	Func<Northwind, string, string, IQueryable<Employees>>

which is the delegate signature for the lambda expression we created ...