relax! it’s just 0s and 1s

so in my last post, I wrote about the Linq compiled queries syntax, before diving into its use. this post is about the usage of compiled queries.

every time, a Linq Query is executed (accessed), the expressions tree/Linq query is translated into its equivalent SQL/data source query and the results are fetched. for static data and data, changing on parameter values, the repeated translation is redundant.

Assume we have the following methods:

GetAllProductsAuthorizedToBeSoldInIndia()
{
 // Linq query to join Products and Country table on key for 
 // India and return the expected products.
}

now if we access this method N times, the LINQ query will have N SQL translations, for the same result that it will fetch. clearly this is a performance bottleneck.

also consider a parameterized method:

GetAllProductsAuthorizedToBeSoldInACountry(int countryId)
{
 // Linq query to join Products and Country table on 
 // CountryId and return the expected products.
}

Except for the parameter value, the generated SQL query has to be re-generated every time.

To solve this, we use Linq Compiled Queries. They facilitate one time translation of the Linq query, and re-use of the translated query across multiple calls.

public class MyCompiledQueries
{
  private static 
  Func<MyDatabaseContext, IQueryable<Product>> 
  ProductsAuthorizedToBeSoldInACountry 
  = CompiledQuery.Compile(
  (MyDatabaseContext db, int countryId) => 
  db.Products(p=>p.CountryId=countryId)); 
 
  public static List<Product>  
  GetAllProductsAuthorizedToBeSoldInACountry
  (MyDatabaseContext db, int countryId)
  {
    return MyCompiledQueries. ProductsAuthorizedToBeSoldInACountry(
    db, countryId).ToList<Product>();
  }
}

the queries are translated once, and the parameter values are substituted at runtime.

1. The CompileQuery.Compile method returns us a delegate, which can be invoked repeatedly.
2. The appropriate parameters need to be passed to the delegate during the invocation. (data context + query parameters)
3. Because we need to reuse the delegate to actually have any performance benefit, static variables/method wrappers are preferred.
4. It is a good practice to put all of your Compiled Queries into a separate class, at one place, and possibly have wrapper methods which make more business sense than entity names.
5. on my box, performance increased by 220%, when I changed my queries to Compiled Queries. I was working on a test domain data application using Linq. this was just slightly less than the data reader performance, we get.

the syntax for compiled queries makes it a little tough to maintain, but the benefits are sweet n totally worth the effort.

I wanted to blog about Compiled queries in LINQ in this post. However, before doing that, I realized it would be good if I wrote something about the foundation on which the Compiled Query syntax is built.

This is because the call to a Compiled Linq Query involves calling the Compile() method:

public static Func<TArg0, TArg1, TResult> 
 
CompiledQuery.Compile<TArg0, TArg1,  TResult>
 
(Expression<Func<TArg0, TArg1, TResult>> query)
 
 where TArg0 : DataContext;

Now if the above syntax makes total sense to you, then you are probably over the following words. Else, you could read on to make sense of the above syntax.

Assume we have a delegate type defined as follows:

delegate int MyDelegateType(int a, int b)

this delegate can contain (thinking of code container) all methods which return ‘int’ and take 2 integer parameters.
E.g.

public int AddMethod(int a, int b)
 
MyDelegateType myDelegateObject = new MyDelegateType(AddMethod);

As you know, C# 2.0, simplifies this synax. we can now do:

MyDelegateType myDelegateObject = AddMethod;

looks good so far..

Now suppose, we make the delegate deal with generic types.

delegate T MyGenericDelegateType<T> (T a, T b)

this delegate can contain all methods which return ‘T’ type and take 2 T type parameters.

MyGenericDelegateType<int> myGenericDelegateObject = AddMethod;

Now, this is the AddMethod

public int AddMethod(int a, int b)
{
  return a + b;
}

As you know using C# 2.0 anonymous methods, we could write:

MyGenericDelegateType<int> myGenericDelegateObject =
delegate (int a, int b) { return a + b };

C# 3.0, further simplifies this anonymous method syntax, by eliminating even the delegate keyword, and getting the => operator. (Lambda Expressions coming in..)

Hence in C# 3.0, we can write:

MyGenericDelegateType<int> myGenericDelegateObject =
(int a, int b) => return a + b;

A further simplification to the above syntax is removing the types declared. This is because C# 3.0 is equipped with the type inference feature.

MyGenericDelegateType<int> myGenericDelegateObject = (a,b) => a + b;

Notice how we removed even the return keyword. This is perfectly valid, if the method contains only a single return statement.

Now the System.Linq namespace already defines some generic delegates for us. e.g. One of them is:

public delegate TResult Func<A0, A1, TResult> (A0 arg0, A1 arg1);

What this means is we don’t need to define our own delegate types, every time we want to use one with a similar signature.

we could directly say:

Func<int, int, int> myLinqDelegateObject = AddMethod;

Here TResult, A0 and A1 are int types. Replacing by lambda expressions,

Func<int, int, int> myLinqDelegateObject = (a,b) => a+b;

We can use this delegate object as

int sum = myLinqDelegateObject(3, 4);  // sum = 7;

Here myLinqDelegateObject is nothing but a delegate object (instance), of a delegate type already defined by the System.Linq namespace.

An Expression Tree is nothing but an object of type

System.Linq.Expressions.Expression<TDelegateType>

, where TDelegateType is the delegate the tree represents.

e.g.

Expression<Func<int, int, int>> myExpressionTreeObject = (a,b) => a+b;

Expression trees are nothing but an in memory representation of code. sort of a mini code segment, which can be evaluated later on demand, and can be built upon.

Based on all the points above, if we see the syntax now:

public static Func<TArg0, TArg1, TResult> 
 
CompiledQuery.Compile<TArg0, TArg1,  TResult>
 
(Expression<Func<TArg0, TArg1, TResult>> query)
 
 where TArg0 : DataContext;

It should look clear that, the Compile method takes in an Expression Tree object (query), of which the first parameter should be of type DataContext (Generic Contraint), and the return type is a Delegate object. Func

So the use of the Compile method would be as follows:

var queryDelegate =  CompiledQuery.Compile(
 
  (MyDataContext db, string param1, string param2) =>
 
  db.MyEntity
     .Where(v => v.Column1.Trim().ToLower() == param1.Trim().ToLower())
     .Where(v => v.Column2.Trim().ToLower() == param2.Trim().ToLower()));

Here queryDelegate is of type:

Func<MyDataContext, string, string, IQueryable<MyEntity>>

and can be used as follows:

var MyEnitities = queryDelegate(db, param1value, param2value);

As you can see, concise delegate syntax ( => ), generics (T), anonymous methods/types (var), type inference (var), lambda expressions (=>) etc, work under the hood for the Compile method. C# 3.0 is cool.

As for the exact use of Linq compiled queries, their advantages and disadvantages, I’ll be writing about it in part 2 of this post..