Query Level Caching For Entity Framework And Windows Azure SQL Database Including Transient Fault Handling

Caching with Entity Framework has always been a challenge. Out of the box Entity Framework doesn’t have second level caching support. Even though, many open source solutions like the Scalable Object Persistence (SOP) Framework exist, I decided to implement a query level cache that uses the Transient Fault Handling Application Block to execute retry policies to transparently handle transient faults.

Keep in mind that transient faults are normal and that its not a question of if they will occur, it’s really a question of when they will occur. SQL Database instances are continuously being shifted around to prevent the service’s performance from degrading.

The Database fluent API I created executes reusable queries. Reusable queries greatly simplify the application’s design by encapsulating query logic in named concepts. They are like Stored Procedures in SQL Server, where team members can discover functionality by reading file names from the query folders. More details about reusable queries can be found in the reference section at the bottom of this post.

The code from this Post is part of the Brisebois.WindowsAzure NuGet Package

To install Brisebois.WindowsAzure, run the following command in the Package Manager Console

PM> Install-Package Brisebois.WindowsAzure

Get more details about the Nuget Package.

Executing Cache Enabled Queries

Building cacheable query using Linq is accomplished by implementing a DatabaseQuery<TResult, TModel>. The Linq query is implemented in the query method. When the  query uses constants to filter data it usually does not require additional Cache Hits. On the other hand, when the generated T-SQL is parameterized, the GenerateCacheHint method needs to be overridden in order to add the parameter values to the cache hint. This will ensure that the query results will be properly identified in the cache.

By default, the base GenerateCacheHint implementation will call ToString on the IQueryable returned by the query method. If the query is implemented using Linq to Entity Framework, the ToString method will return the T-SQL statement that is sent to SQL Database.

The following reusable query does what it sais it does. It finds podcasts that have been published less that 5 days ago. The same query will be implemented using T-SQL in the next example.

public class FindLatestMp3 : DatabaseQuery<Mp3, DemoModel>
{
    private readonly DateTime fiveDaysAgo;

    public FindLatestMp3()
    {
        var fiveDays = TimeSpan.FromDays(5);
        fiveDaysAgo = DateTime.UtcNow.Subtract(fiveDays); 
    }

    protected override IQueryable<Mp3> Query(DemoModel model)
    {
        if (model == null)
            throw new ArgumentNullException("model");

        return model.Mp3s.Where(m => m.Date > fiveDaysAgo);
    }

    protected override string GenerateCacheHint()
    {
        return base.GenerateCacheHint() + fiveDaysAgo;
    }
}

Executing the query is accomplished by using the Database fluent API I created to execute reusable queries. The model creation logic is injected by means of a lambda. This enables you to configure the DbContext. Then to activate caching, call the WithCache method. This method will wrap your query execution logic with caching logic. Then call the QueryAsync method passing your reusable query instance as the parameter.

Its important to note that in the first release of the Database fluent API, caching uses the MemoryCache from the .Net Framework. There are plans to enable you to choose your own cache container. Furthermore, its quite important to note that lazy loading does not work well with the Database fluent API. This is because once the QueryAsync method has completed, the model is disposed.

public async Task<Mp3> FindMostRecentMp3()
{
    var mp3s = await Database<DemoModel>
                            .Model(() => new DemoModel())
                            .WithCache()
                            .QueryAsync(new FindLatestMp3s());
    return mp3s.First();
}

Building a cacheable query using T-SQL is accomplished by implementing a DatabaseQuery<TResult, TModel> and by overriding the GenerateCacheHint method. Be sure to include enough cache hints to enable the API to generate a unique cache key for the query. Without a unique cache key, the cache container will return the wrong results for you queries.

public class SqlFindLatestMp3 : DatabaseQuery<Mp3, DemoModel>
{
    private const string SP = "SELECT * FROM [MP3] WHERE [Date] >= {0}";

    private readonly DateTime fiveDaysAgo;

    public SqlFindLatestMp3()
    {
        var fiveDays = TimeSpan.FromDays(5);
        fiveDaysAgo = DateTime.UtcNow.Subtract(fiveDays);
    }

    protected override IQueryable<Mp3> Query(DemoModel model)
    {
        if (model == null)
            throw new ArgumentNullException("model");

        return model.Database.SqlQuery<Mp3>(SP, fiveDaysAgo)
                                .AsQueryable();
    }

    protected override string GenerateCacheHint()
    {
        return string.Format(CultureInfo.InvariantCulture, 
                                SP, 
                                fiveDaysAgo);
    }
}

Executing the T-SQL based query is done as follows.

public async Task<Mp3> SQLFindMostRecentMp3()
{
    var mp3s = await Database<DemoModel>
                            .Model(() => new DemoModel())
                            .WithCache()
                            .QueryAsync(new SqlFindLatestMp3());
    return mp3s.First();
}

 

Debugging Cached Queries

Debugging cached queries can be a challenge! To ease debugging efforts, the Database fluent API produces a trace if the debugger is attached. If not the trace is by passed and does not affect the performance of production code. Furthermore, the trace is only produced if the WithCache method is called before the QueryAsync method.

Debug Trace:

Query as String :
SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Description] AS [Description], 
[Extent1].[Title] AS [Title], 
[Extent1].[Date] AS [Date]
FROM [dbo].[Mp3] AS [Extent1]
WHERE [Extent1].[Date] > @p__linq__0


Query Cache Key Hint :
SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Description] AS [Description], 
[Extent1].[Title] AS [Title], 
[Extent1].[Date] AS [Date]
FROM [dbo].[Mp3] AS [Extent1]
WHERE [Extent1].[Date] > @p__linq__
03/15/2013 8:54:10 PM
Data Source=(localdb)\v11.0;
Initial Catalog=Brisebois.WindowsAzure.Tests.DemoModel;
Integrated Security=True;
MultipleActiveResultSets=True;
Application Name=EntityFrameworkMUE


Hashed CacheHint :
vz/nOcwJO6UL8+/s+jrwAhxXe3k=

Query result from server

The last line from the trace will specify whether the results came from cache or from the origin server.

Query result from cache

 

Executing Queries Without Cache

Executing queries without cache is done by not calling the WithCache method.

public async Task<Mp3> FindMostRecentMp3()
{
    var mp3s = await Database<DemoModel>
                            .Model(() => new DemoModel())
                            .QueryAsync(new FindLatestMp3s());
    return mp3s.First();
}

Executing a query that does not require caching will enable the use lambda expressions. Be sure to force the execution of the query by calling the ToList method or by forcing an enumeration of the IQueryable. This is important because the model is disposed within the scope of the QueryAsync method.

public async Task<Mp3> LambdaFindMostRecentMp3()
{
    var fiveDays = TimeSpan.FromDays(5);
    var fiveDaysAgo = DateTime.UtcNow.Subtract(fiveDays);

    var mp3s = await Database<DemoModel>
                      .Model(() => new DemoModel())
                      .QueryAsync(model =>
                          {
                              return model.Mp3s
                                          .Where(m=> m.Date > fiveDaysAgo)
                                          .ToList();
                          });
    return mp3s.First();
}

Using a Model for More Than a Single Query

Using a model for more than one query is possible. Keep in mind that Entity Framework is not thread safe. Therefor, execute queries synchronously. Calling the PreserveModel method will prevent the QueryAsync method from disposing the model.

Keeping a model around for multiple queries can be interesting, because it allows us to query Entity Framework’s local cache, preventing us from making un necessary calls to the database.

public async Task<ICollection<Mp3>> FindMostRecentMp3MultipleQuery()
{
    var query = new FindLatestMp3();
            
    using (var model = new DemoModel())
    {
        var result1 = await Database<DemoModel>.Model(() => model)
                                            .PreserveModel()
                                            .QueryAsync(query);

        //same query with caching with the same model instance

        return await Database<DemoModel>.Model(() => model)
                                            .PreserveModel()
                                            .WithCache()
                                            .QueryAsync(query);
    }
}

 

The Model & Entity

public class DemoModel : DbContext
{
    public DbSet<Mp3> Mp3s { get; set; }
}

public class Mp3
{
    public int Id { get; set; }
    public string Description { get; set; }
    public string Title { get; set; }
    public DateTime Date { get; set; }
}

Seeding The Model

[TestInitialize]
public void Setup()
{
     var uri = "http://feeds.feedburner.com/netRocksFullMp3Downloads";
     var xmlTextReader = new XmlTextReader(uri);
     var feed = SyndicationFeed.Load(xmlTextReader);
    
     if (feed == null) Assert.Fail();
    
     Database.SetInitializer(new CreateDatabaseIfNotExists<DemoModel>());
    
     using (var model = new DemoModel())
     {
         feed.Items.ToList().ForEach(item =>
         {
             var mp3 = new Mp3
             {
                 Date = item.PublishDate.UtcDateTime,
                 Description = item.Summary.Text,
                 Title = item.Title.Text
             };
    
             model.Mp3s.Add(mp3);
         });
               
         model.SaveChanges();
     }
}

 

References

• Build Reusable, Testable Queries Part 1
• Build Reusable, Testable Queries Part 2