Linq to Sql CompiledQuery container
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;
}
}
}
}
Comments
Anonymous
February 19, 2009
Merci pour cette classe. J'etais parti à faire un peu le même genre de container mais en faisant du casting à tout va.... Evidement l'utilisation des types generic permet une meilleure perf !Anonymous
May 10, 2009
Your first picture distracts me from reading the rest of your article because it is so beautiful! Now, since I need to know what you are talking about, I must return to the work at hand. Thanks for sharing.Anonymous
May 11, 2009
Thanks Peter, Maybe I should share a picture per article :pAnonymous
October 14, 2009
Thanks for sharing this code.Really nice example of complied query with dictionary.Anonymous
January 03, 2010
The comment has been removedAnonymous
February 16, 2010
WOW! Beautiful! Did exactly what I was wanting to do...Anonymous
March 18, 2010
Now, that was a great "duh" moment for me. Excellent code! Just read the MSDN article for March 2010 that talked about CompiledQuery, and the gotcha about letting the Func<> fall out of scope, and loosing the performance benefit of the compiling in the first place. I was just about to create this exact thing, and low and behold, you already did it. Excellent man!Anonymous
April 08, 2010
This is great. An initial load in my app came down from 44 seconds to 10 seconds. Thanks to your code.Anonymous
September 10, 2010
LINQ to SQL CompiledQuery is good option, but things that needs to be taken into consideration while using the same is that first time when it is called it takes more time than a routine query would take. Refer to below link... www.a2zmenu.com/.../LINQ%20to%20SQL%20CompiledQuery.aspxAnonymous
September 11, 2010
Yes first call always takes more time. Compiled queries improves next calls performance bye keeping it in cache. That's what this article is about. No ? :)