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..

I had a stored procedure which returned multiple result sets.

the stored proc in short, looked as follows: (trimmed down for relevant code)

CREATE PROCEDURE SELMessages
AS
BEGIN
 
   -- prefix blah blah
 
 Declare @Messages TABLE
 (
       MessageID int,
       MessageName nvarchar(256)
 )
 
 Declare @MessageRecipients TABLE
 (
       MessageID int,
       RecipientName nvarchar(256)
 )
 
    -- interim blah blah
 
    -- Result Set 1
    SELECT  MessageID, MessageName FROM @Messages
   
    -- Result Set 2
    SELECT  MessageID, RecipientName FROM @MessageRecipients
 
END
GO

using Linq to SQL (or DLinq or L2S) i had to use this stored procedure.
I dragged the SProc into the designer, and tried to use it in the code. the Linq to SQL designer had generated the following code:

[System.Data.Linq.Mapping.DatabaseAttribute(Name="MessageDB")]
public partial class MessageDBDataContext : System.Data.Linq.DataContext
{
 
 private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
 
        // more code
 
 [Function(Name="dbo.SELMessages")]
 public ISingleResult SELMessages()
 {
  IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
  return ((ISingleResult)(result.ReturnValue));
 }
 
        // more code
}

the result to be written was a class which looked like:

public partial class SELMessagesResult
{  
 private int _MessageID;
 
 private string _MessageName;
 
 public SELMessageRecipientsResult()
 {
 }
 
 [Column(Storage="_MessageID", DbType="Int NOT NULL")]
 public int MessageID
 {
  get
  {
   return this._MessageID;
  }
  set
  {
   if ((this._MessageID != value))
   {
    this._MessageID = value;
   }
  }
 }
 
 [Column(Storage="_MessageName", DbType="NVarChar(256) NOT NULL", CanBeNull=false)]
 public string MessageName
 {
  get
  {
   return this._MessageName;
  }
  set
  {
   if ((this._MessageName != value))
   {
    this._MessageName = value;
   }
  }
 }
}

my code snippet to use the Stored proc was as follows:

using (MessageDBDataContext messageDbDataContext = new MessageDBDataContext())
{
    return messageDbDataContext.SELMessages();
}

the above code, did not give me the expected behavior. it failed to identify the 2 result sets.

as you can see, the return type of the method is just the result SELMessagesResult,which represents our first result set only.

 I tried to solve the issue and found this post.

it pretty much solved my problem. the only difference is that, instead of the existing tables being returned, my Stored Proc returned  result sets based on some temporary tables it was creating.

based on the post, these were the steps, I followed:

• created the following class to represent the first result set. the designer had already created this class. (SELMessagesResult)
• created the following class to represent the second result set. SELMessageRecipientsResult)

 public partial class SELMessageRecipientsResult
{
    private int _MessageID;
 
    private string _MessageRecipient;
 
    public SELMessageRecipientsResult()
    {
    }
 
    [Column(Storage = "_MessageID", DbType = "Int NOT NULL")]
    public int MessageID
    {
        get
        {
            return this._MessageID;
        }
        set
        {
            if ((this._MessageID != value))
            {
                this._MessageID = value;
            }
        }
    }
 
    [Column(Storage = "_MessageRecipient", DbType = "NVarChar(256) NOT NULL", CanBeNull = false)]
    public string MessageRecipient
    {
        get
        {
            return this._MessageRecipient;
        }
        set
        {
            if ((this._MessageRecipient != value))
            {
                this._MessageRecipient = value;
            }
        }
    }
}

• created a partial class obviously named same as my Data Context class. (right click on .dbml >> view code)

 partial class MessageDBDataContext
 {
 }

• added a new method to this class with following signature:

public IMultipleResults SELMessagesWithMultipleRS()

• Attributed the method with the following code:

[ResultType(typeof(SELMessagesResult))]
[ResultType(typeof(SELMessageRecipientsResult))]
[Function(Name = "dbo.SELMessages")]
public IMultipleResults SELMessagesWithMultipleRS()

• the full method looks as follows: 

partial class MessageDBDataContext
{
    [ResultType(typeof(SELMessagesResult))]
    [ResultType(typeof(SELMessageRecipientsResult))]
    [Function(Name = "dbo.SELMessages")]
    public IMultipleResults SELMessagesWithMultipleRS()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return ((IMultipleResults)(result.ReturnValue));
    }
}

• As the post explains, used the new method as follows:

using (MessageDBDataContext messageDbDataContext = new MessageDBDataContext())
{
    // Get both the result set.
    var results = messageDbDataContext.SELMessagesWithMultipleRS();
 
    // Get the first result.
    List messages = results.GetResult().ToList();
 
    // Get the second result.
    List messageRecipients = results.GetResult().ToList();
 
    // Do the necessary processing with the 2 sequences.
 
}

spot on.. all was in place and I tested the code for expected behavior.

notes:

• since the original data context designer class is auto-generated by the LINQ to SQL designer, it is good to create a partial class. that way, you need not create it every time you change the DBML.
• you don’t need to attribute the class with the ‘DatabaseAttribute‘ Attribute. .NET is happy with a single annotation.
• you don’t need an instance of the DataContext in this class. (since it is a partial class)

hopefully, this issue will be resolved in their next release of Linq to SQL designer.

there’s a solution to every problem; given enough time and money..