Automatically Compile Linq Queries
I had to deal with saving a > 15y/o project that was developed using LinqToSql and was too CPU hungry.
Benchmarking showed that using compiled query is x7 faster for complex queries, and x2 for simple queries (considering that the running the query itself is negligible, here it's just about the throughput of compiling the query).
Caching is NOT done automatically by .Net Framework (no matter what version), this only happens for Entity Framework NOT for LINQ-TO-SQL, and these are different technologies.
Usage of compiled queries is tricky, so here are two important highlights:
- You MUST compile que query including the materialization instructions (FirstOrDefault/First/Any/Take/Skip/ToList), otherwise you risk bringing your whole database into memory: LINQ to SQL *compiled* queries and when they execute
- You cannot DOUBLE iterate on a compiled query's result (if it's an IQueryable), but this is basically solved once you properly consider the previous point
Considering that, I came up with this cache class. Using the static approach as proposed in other comments has some maintainability drawbacks - it's mainly less readable -, plus it is harder to migrate an existing huge codebase.
LinqQueryCache<VCDataClasses>
.KeyFromQuery()
.Cache(
dcs.CurrentContext,
(ctx, courseId) =>
(from p in ctx.COURSEs where p.COURSEID == courseId select p).FirstOrDefault(),
5);
On very tight loops, using a cache key from the callee instead of the query itself yielded +10% better performance:
LinqQueryCache<VCDataClasses>
.KeyFromStack()
.Cache(
dcs.CurrentContext,
(ctx, courseId) =>
(from p in ctx.COURSEs where p.COURSEID == courseId select p).FirstOrDefault(),
5);
And here is the code. The cache prevents the coder from returning an IQueryable in a compiled query, just for safety.
public class LinqQueryCache<TContext>
where TContext : DataContext
{
protected static readonly ConcurrentDictionary<string, Delegate> CacheValue = new ConcurrentDictionary<string, Delegate>();
protected string KeyValue = null;
protected string Key
{
get => this.KeyValue;
set
{
if (this.KeyValue != null)
{
throw new Exception("This object cannot be reused for another key.");
}
this.KeyValue = value;
}
}
private LinqQueryCache(string key)
{
this.Key = key;
}
public static LinqQueryCache<TContext> KeyFromStack(
[System.Runtime.CompilerServices.CallerFilePath] string sourceFilePath = "",
[System.Runtime.CompilerServices.CallerLineNumber] int sourceLineNumber = 0)
{
return new LinqQueryCache<TContext>(Encryption.GetMd5(sourceFilePath + "::" + sourceLineNumber));
}
public static LinqQueryCache<TContext> KeyFromQuery()
{
return new LinqQueryCache<TContext>(null);
}
public T Cache<T>(TContext db, Expression<Func<TContext, T>> q)
{
if (Debugger.IsAttached && typeof(T).IsAssignableFrom(typeof(IQueryable)))
{
throw new Exception("Cannot compiled queries with an IQueryableResult");
}
if (this.Key == null)
{
this.Key = q.ToString();
}
if (!CacheValue.TryGetValue(this.Key, out var result))
{
result = CompiledQuery.Compile(q);
CacheValue.TryAdd(this.Key, result);
}
return ((Func<TContext, T>)result)(db);
}
public T Cache<T, TArg1>(TContext db, Expression<Func<TContext, TArg1, T>> q, TArg1 param1)
{
if (Debugger.IsAttached && typeof(T).IsAssignableFrom(typeof(IQueryable)))
{
throw new Exception("Cannot compiled queries with an IQueryableResult");
}
if (this.Key == null)
{
this.Key = q.ToString();
}
if (!CacheValue.TryGetValue(this.Key, out var result))
{
result = CompiledQuery.Compile(q);
CacheValue.TryAdd(this.Key, result);
}
return ((Func<TContext, TArg1, T>)result)(db, param1);
}
}
You can't have extension methods invoked on anonymous lambda expressions, so you'll want to use a Cache class. In order to properly cache a query you'll also need to 'lift' any parameters (including your DataContext) into parameters for your lambda expression. This results in very verbose usage like:
var results = QueryCache.Cache((MyModelDataContext db) =>
from x in db.Foo where !x.IsDisabled select x);
In order to clean that up, we can instantiate a QueryCache on a per-context basis if we make it non-static:
public class FooRepository
{
readonly QueryCache<MyModelDataContext> q =
new QueryCache<MyModelDataContext>(new MyModelDataContext());
}
Then we can write a Cache method that will enable us to write the following:
var results = q.Cache(db => from x in db.Foo where !x.IsDisabled select x);
Any arguments in your query will also need to be lifted:
var results = q.Cache((db, bar) =>
from x in db.Foo where x.id != bar select x, localBarValue);
Here's the QueryCache implementation I mocked up:
public class QueryCache<TContext> where TContext : DataContext
{
private readonly TContext db;
public QueryCache(TContext db)
{
this.db = db;
}
private static readonly Dictionary<string, Delegate> cache = new Dictionary<string, Delegate>();
public IQueryable<T> Cache<T>(Expression<Func<TContext, IQueryable<T>>> q)
{
string key = q.ToString();
Delegate result;
lock (cache) if (!cache.TryGetValue(key, out result))
{
result = cache[key] = CompiledQuery.Compile(q);
}
return ((Func<TContext, IQueryable<T>>)result)(db);
}
public IQueryable<T> Cache<T, TArg1>(Expression<Func<TContext, TArg1, IQueryable<T>>> q, TArg1 param1)
{
string key = q.ToString();
Delegate result;
lock (cache) if (!cache.TryGetValue(key, out result))
{
result = cache[key] = CompiledQuery.Compile(q);
}
return ((Func<TContext, TArg1, IQueryable<T>>)result)(db, param1);
}
public IQueryable<T> Cache<T, TArg1, TArg2>(Expression<Func<TContext, TArg1, TArg2, IQueryable<T>>> q, TArg1 param1, TArg2 param2)
{
string key = q.ToString();
Delegate result;
lock (cache) if (!cache.TryGetValue(key, out result))
{
result = cache[key] = CompiledQuery.Compile(q);
}
return ((Func<TContext, TArg1, TArg2, IQueryable<T>>)result)(db, param1, param2);
}
}
This can be extended to support more arguments. The great bit is that by passing the parameter values into the Cache method itself, you get implicit typing for the lambda expression.
EDIT: Note that you cannot apply new operators to the compiled queries.. Specifically you cannot do something like this:
var allresults = q.Cache(db => from f in db.Foo select f);
var page = allresults.Skip(currentPage * pageSize).Take(pageSize);
So if you plan on paging a query, you need to do it in the compile operation instead of doing it later. This is necessary not only to avoid an exception, but also in keeping with the whole point of Skip/Take (to avoid returning all rows from the database). This pattern would work:
public IQueryable<Foo> GetFooPaged(int currentPage, int pageSize)
{
return q.Cache((db, cur, size) => (from f in db.Foo select f)
.Skip(cur*size).Take(size), currentPage, pageSize);
}
Another approach to paging would be to return a Func:
public Func<int, int, IQueryable<Foo>> GetPageableFoo()
{
return (cur, size) => q.Cache((db, c, s) => (from f in db.foo select f)
.Skip(c*s).Take(s), c, s);
}
This pattern is used like:
var results = GetPageableFoo()(currentPage, pageSize);
Since nobody is attempting, I'll give it a shot. Maybe we can both work this out somehow. Here is my attempt at this.
I set this up using a dictionary, I am also not using DataContext although this is trivial i believe.
public static class CompiledExtensions
{
private static Dictionary<string, object> _dictionary = new Dictionary<string, object>();
public static IEnumerable<TResult> Cache<TArg, TResult>(this IEnumerable<TArg> list, string name, Expression<Func<IEnumerable<TArg>, IEnumerable<TResult>>> expression)
{
Func<IEnumerable<TArg>,IEnumerable<TResult>> _pointer;
if (_dictionary.ContainsKey(name))
{
_pointer = _dictionary[name] as Func<IEnumerable<TArg>, IEnumerable<TResult>>;
}
else
{
_pointer = expression.Compile();
_dictionary.Add(name, _pointer as object);
}
IEnumerable<TResult> result;
result = _pointer(list);
return result;
}
}
now this allows me to do this
List<string> list = typeof(string).GetMethods().Select(x => x.Name).ToList();
IEnumerable<string> results = list.Cache("To",x => x.Where( y => y.Contains("To")));
IEnumerable<string> cachedResult = list.Cache("To", x => x.Where(y => y.Contains("To")));
IEnumerable<string> anotherCachedResult = list.Cache("To", x => from item in x where item.Contains("To") select item);
looking forward to some discussion about this, to further develop this idea.
For future posterity : .NET Framework 4.5 will do this by default (according to a slide in a presentation I just watched).