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.

2 comments:

  1. You don't need this. You can also write

    private static readonly Func> _compiledQuery =
    CompiledQuery.Compile((DataContext db, int refId) =>
    db.Notes
    .Where( note => note.CaseId == refId )
    .Select(note => note.Text));

    This way, the creation of the object holding the compiled query is not lazy, but it is automatically thread safe.

    Note however that the compilation of the query is still lazy: the compiled query object is just a placeholder for the result of this compilation; the actual compilation happens upon first execution of the query. The result is then stored in the compiled query object for subsequent reuse.

    The fact that the creation of the object is not lazy doesn't matter, this creation is extremely cheap (in memory as well as CPU usage).

    ReplyDelete
  2. Thanks for the information about CompiledQuery.Compile. If I ever revisit the code I'll update it, as it's very clean and readable. I learn new stuff about the .Net framework every day :)

    ReplyDelete