Saturday, August 18, 2012

Linq to Sql Batch Future Queries

Batch queries are crucial feature of any good ORM...including LinqToSql!

Future queries are an unobtrusive and easy to use way of batching database queries into a lazy loaded data structures. Future queries were originally a feature of NHibernate, and have since been ported to other ORMs. However I am currently working on a project where I do not have access to those tools.

Thus I created LinqToSql.Futures, a simple extension that adds batched futures queries to LinqToSql.

Using Future Queries

  1. Get the LinqToSqlFutures NuGet package.
  2. Extend your DataContext to implement IFutureDataContext. (This is optional, see below!)
  3. Use future queries!

To batch queries together, simply call the ToFuture extension method on your IQueryables, this will return a Lazy collection of entities. The first time that one of the Lazy collections is accessed, all of the pending queries will be batched and executed together.

[Fact]
public void ToFuture()
{
    // SimpleDataContext Implements IFutureDataContext 
    using (var dataContext = new SimpleDataContext())
    {
        Lazy<IList<Person>> people = dataContext.Persons
            .Where(p => p.FirstName == "Tom" || p.FirstName == "Cat")
            .ToFuture();
 
        Lazy<IList<Pet>> pets = dataContext.Pets
            .Where(p => p.Name == "Taboo")
            .ToFuture();
 
        // Single database call!
        Assert.Equal(2, people.Value.Count);
        Assert.Equal(1, pets.Value.Count);
    }
}

To see the future queries working, you can use SqlProfiler to capture the batch query:

Extending Your DataContext

To use the code above, you need only extend your generated DataContext to implement the IFutureDataContext interface (which consists of a mere one property). Additionally, the sample code below overrides the Dispose method to optimize query disposal.

partial class SimpleDataContext : IFutureDataContext
{
    protected override void Dispose(bool disposing)
    {
        if (_futureCollection != null)
        {
            _futureCollection.Dispose();
            _futureCollection = null;
        }
 
        base.Dispose(disposing);
    }
 
    private IFutureCollection _futureCollection;
    public IFutureCollection FutureCollection
    {
        get
        {
            if (_futureCollection == null)
                _futureCollection = this.CreateFutureCollection();
 
            return _futureCollection;
        }
    }
}

Do you not have access to extend your DataContext? No problem...

Alternative Consumption

Your DataContext does not need to implement IFutureDataContext to use future queries. You can also create a FutureCollection and pass that into the ToFuture method. This will provide the future queries with a batch context.

[Fact]
public void FutureCollection()
{
    using (var dataContext = new SimpleDataContext())
    using (var futureCollcetion = dataContext.CreateFutureCollection())
    {
        Lazy<IList<Person>> people = dataContext.Persons
            .Where(p => p.FirstName == "Tom" || p.FirstName == "Cat")
            .ToFuture(futureCollcetion);
 
        Lazy<IList<Pet>> pets = dataContext.Pets
            .Where(p => p.Name == "Taboo")
            .ToFuture(futureCollcetion);
 
        // Single database call!
        Assert.Equal(2, people.Value.Count);
        Assert.Equal(1, pets.Value.Count);
    }
}

Links

If you enjoy the future queries or need to extend them, then please feel free to download the source from GitHub.

LinqToSql.Futures on GitHub
LinqToSql.Futures on NuGet

Shout it

Enjoy,
Tom

No comments:

Post a Comment

Real Time Web Analytics