Tuesday, April 6, 2010

Compiling Linq to SQL the Lazy Way

In the March issue of MSDN magazine there was an article about precompiling Linq queries in order to optimize query speed for queries being executes numerous times.

This was perfect for the current project I’m working with, and I set out to change my code which originally looked like this:

string Original(int refId)
{
var query = DbContext.Notes
.Where( note => note.CaseId == refId )
.Select(note => note.Text);
return string.Join(";", query);
}

Creating a static compiled query along the lines of the article changed the code to this:

private static Func<DataContext, int, IEnumerable<string>> _compiledQuery;
private Func<DataContext, int, IEnumerable<string>> GetQuery()
{
if (_compiledQuery == null)
{
_compiledQuery = CompiledQuery.Compile((DataContext db, int refId) =>
db.Notes
.Where( note => note.CaseId == refId )
.Select(note => note.Text));
}
return _compiledQuery;
}

string Compiled(int refId)
{
var query = GetQuery().Invoke(DbContext, refId);
return string.Join(";", query);
}

This is your regular code with checking if it’s been created and if not instantiate it. What I don’t like with this approach, now that I’m a .Net 4.0 guy, is that you might compile it twice if two threads access it at the same time since it’s not thread safe. Putting double locking in there would also cloud readability.

Certainly no big issue, but since we now have the wonderful Lazy<T> operator we can write the code like this instead:

private static Lazy<Func<DataContext, int, IEnumerable<string>>> NotesQuery = new Lazy<Func<DataContext, int, IEnumerable<string>>>(
() => CompiledQuery.Compile((DataContext db, int refId) =>
db.Notes
.Where( note => note.CaseId == refId )
.Select(note => note.Text))

);

string Lazy(int refId)
{
var query = NotesQuery.Value.Invoke(DbContext, refId);
return string.Join(";", query);
}

Not as clean as the first version, but certainly less messy than the intermediate one. Using Lazy<T> on shared instances is a good way to ensure it’s created and to avoid threading issues. And if you never use it, which could be the case for a function in a general busuiness layer, you won't compile it if you don't need it.

If we could hide some of the signature it would look and read even better.