Linq to Sql CompiledQuery container - Mitsu's blog - Site Home

Before we go deep into Linq to Sql, I wanted to share with you one of my pictures made last week at Chamonix Mont Blanc from "L'aiguille du midi" during some days off.

Ok now let's go. Here is just a little trick but with some interesting patterns that could be useful in some other contexts not connected to Linq to Sql.

When using Linq expressions, like with Linq to Sql, translating the expression into something else (sql for example) is taking time and resources. Sometimes it's negligible, sometimes not...

Regarding Linq to Sql, let's say that if your query is called more that once, it's always a good thing to use CompiledQueries.

The idea is simple: translating the query at first call then keeping the generated sql for next calls. This logic is deferred in a delegate provided by the Compile() method.

In the following example, the second query execution (with "London") is extremely efficient because the sql query is cached after the first call occurred.

void Load()
{
    var db = new NorthwindDataContext();
    db.Log = Console.Out;

    var q = 
        CompiledQuery.Compile((NorthwindDataContext context, string city) =>
        from c in context.Customers
        where c.CustomerID == city
        select c);

    var result = q(db, "Paris").ToList();
    var result2 = q(db, "London").ToList();
}

But I often hear developers saying the syntax is too complex. The Compile() method is waiting for an "Expression<Func<TArg0, TArgX..., TResult>> where TArg0 : DataContext". What your must understand here is that you must provide a function taking a Linq to Sql argument as first parameter and the your are free to provide up to 4 more arguments if your query needs it.

In my first example we understand easily the use of the CompiledQuery but we may want more ! "q" is a local variable so at each call of the Load() method the compiled query is recreated. We could keep the compiled query in a larger scope or even statically if it's worth doing it. But another problem appears. If we want to store the compiled query in a class field, static or not, we have to fully define its type without using type inference (var). The compiled query return value type is not easy to know. Let's see why.

The returning type if Func<TArg0, TArgX..., TResult> which does not seem to be so complex but TResult is inferred from the Linq to Sql query itself.

For example, in our very simple code the real type of "q" is :

Func<NorthwindDataContext, IQueryable<Customer>>

But depending on your query, it could easily become:

Func<NorthwindDataContext, IOrderedQueryable<IGrouping<Customer>>>

It's not easy to write and worst, you may have to update your declaration if you rewrite your query...

So what I propose here is a solution to keep a global fully typed reference on a compiled query with still taking advantages of the type inference.

The first idea is to store a dictionary of compiled queries statically somewhere. Here, I will use keys of type string but you could easily change it to any other type. Compiled queries are delegates, so for the moment let's just define our dictionary as Dictionary<string, Delegate>.

public static class MyQueries
{
    private static Dictionary<string, Delegate> compiledQueries =
        new Dictionary<string, Delegate>();
}

Adding a compiled query to this dictionary is easy but we will loose static type information. Of course the compiled queries instances will still be typed but we want to have this information at compile time to have intellisense.

So my idea here is to resolve everything in a single method call :

public static class MyQueries
{
    private static Dictionary<string, Delegate> compiledQueries =
        new Dictionary<string, Delegate>();

    public static Func<TArg0, TResult> Get<TArg0, TResult>(string key,
        Expression<Func<TArg0, TResult>> query) where TArg0 : DataContext)
    {
        Delegate d = null;
        if (compiledQueries.TryGetValue(key, out d))
            return (Func<TArg0, TResult>)d;
        else
        {
            var result = CompiledQuery.Compile(query);
            compiledQueries.Add(key, result);
            return result;
        }
    }
}

Let's use it:

public void Load()
{
    var db = new NorthwindDataContext();
    db.Log = Console.Out;

    var q =
        MyQueries.Get("cust", (NorthwindDataContext context, string city) =>
        from c in context.Customers
        where c.CustomerID == city
        select c);

    var result = q(db, "Paris").ToList();
    var result2 = q(db, "London").ToList();
}

What's happening here ? I have prototyped the MyQueries.Get() method with two parameters. The key identifying the query and then the query itself.
At first call (key not found), we just add the query to the our dictionary.

On next calls (key found), we just cast the query back to its original reference type.
The interesting stuff is here. As we resolve everything in this single method, we always have the generic context resolved. If you look more what's happening when the key is found, you will realize that the query passed as parameter is not used by the our code but is essential because it's used by the compiler type inference to resolve the generic parameters !

So "q" is fully typed and we can use 'q(db, "Paris")' the same way we did previously.

Now let's see some options :

The CompiledQuery.Compile() method has a lot of overloaded versions. This allows you to pass from 1 to 4 parameters.

Compile<TArg0, TResult>();
Compile<TArg0, TArg1, TResult>();
Compile<TArg0, TArg1, TArg2, TResult>();
Compile<TArg0, TArg1, TArg2, TArg3, TResult>();

So I have reflected this possibility into my MyQueries.Get<>() method to offer the same advantages.Then when I came to implementation, I had quite a very hard problem to solve.

What I could have done is to make an ugly copy & paste 4 times of my method, just adding TArg1 then TArg2, etc.

Of course I wanted to factorize this code in a single method that I would have call from all the overloaded versions of MyQueries.Get<>(). Just try to solve this and you will see it's quite complicated...

Let's see exactly in the code where the generic parameters are used. In fact they are used twice. First to cast the delegate when it is found, this one is explicit, then by the CompileQuery.Compile() to resolve its generic parameters using type inference, this one is implicit.

...
        if (compiledQueries.TryGetValue(key, out d))
            return (Func<TArg0, TResult>)d;
        else
        {
            var result = CompiledQuery.Compile(query);
            compiledQueries.Add(key, result);
            return result;
        }
...

The first one could be factorized by creating a new level of genericness, replacing Func<TArg0, TResult> by a TDelegate. But we have a some issues. We have a constraint on TArg0 (where TArg0 : DataContext) that we won't be able to define any more.
Worst, the type inference that solves the generic parameters of the CompiledQuery.Compile() method won't work any more.

A first possible solution is to use reflection intensively to do dynamically what type inference is doing at compile time: find the right version of the CompiledQuery.Compile() method.

But I wanted to find a solution without calling reflection API. So here is what a propose.
Here is an internal version of the Get() method that will factorize our main features. As you can see, I have removed all kind of genericness from the InternalGet() signature.

        private static Delegate InternalGet(string key,
            Func<Delegate> queryProvider)
        {
            Delegate d = null;
            if (compiledQueries.TryGetValue(key, out d))
                return d;
            else
            {
                var result = queryProvider();
                compiledQueries.Add(key, result);
                return result;
            }
        }

I have also remove what was painful: the creation the CompiledQuery, that I have deferred in a Func<Delegate> called queryProvider. Doing this, I will keep the code that is creating the CompiledQuery at the caller level where the compiler has all the generic context available. The final cast, back to Func<TArgxxx, ..., TResult> will also be applied by each calling method.

Now we can write:

    public static Func<TArg0, TResult> Get<TArg0, TResult>(
        string key, Expression<Func<TArg0, TResult>> query)
            where TArg0 : DataContext
    {
        return (Func<TArg0, TResult>) InternalGet(
            key,
            () => CompiledQuery.Compile(query));
    }

and just repeat the same for Get<TArg0, TArg1, TResult>() and so on...

Just to finish, let's make the code more secure:

- make sure the key is not null.
- make the access to the static dictionary thread safe.

I am only adding this now because I did not want to make previous code too heavy.

The whole solution is very short so I did not attached any test solution. Here is the whole code that you can just copy & paste:

public static class MyQueries
{
    private static Dictionary<string, Delegate> compiledQueries =
        new Dictionary<string, Delegate>();

    public static Func<TArg0, TResult> Get<TArg0, TResult>(string key, Expression<Func<TArg0, TResult>> query) where TArg0 : DataContext
    {
        return (Func<TArg0, TResult>) InternalGet(key, () => CompiledQuery.Compile(query));
    }
    public static Func<TArg0, TArg1, TResult> Get<TArg0, TArg1, TResult>(string key, Expression<Func<TArg0, TArg1, TResult>> query) where TArg0 : DataContext
    {
        return (Func<TArg0, TArg1, TResult>)InternalGet(key, () => CompiledQuery.Compile(query));
    }
    public static Func<TArg0, TArg1, TArg2, TResult> Get<TArg0, TArg1, TArg2, TResult>(string key, Expression<Func<TArg0, TArg1, TArg2, TResult>> query) where TArg0 : DataContext
    {
        return (Func<TArg0, TArg1, TArg2, TResult>)InternalGet(key, () => CompiledQuery.Compile(query));
    }
    public static Func<TArg0, TArg1, TArg2, TArg3, TResult> Get<TArg0, TArg1, TArg2, TArg3, TResult>(string key, Expression<Func<TArg0, TArg1, TArg2, TArg3, TResult>> query) where TArg0 : DataContext
    {
        return (Func<TArg0, TArg1, TArg2, TArg3, TResult>)InternalGet(key, () => CompiledQuery.Compile(query));
    }

    private static Delegate InternalGet(string key, Func<Delegate> queryProvider)
    {
        if (string.IsNullOrEmpty(key))
            throw new ArgumentNullException("key");

        lock ((compiledQueries.Keys as ICollection).SyncRoot)
        {
            Delegate d = null;
            if (compiledQueries.TryGetValue(key, out d))
                return d;
            else
            {
                var result = queryProvider();
                compiledQueries.Add(key, result);
                return result;
            }
        }
    }
}