LLBLGen Pro v4.0 feature highlights: Resultset Caching

This is the second post of a series about the new features in LLBLGen Pro v4.0, which was released on April 6th, 2013. Today I'd like to highlight a new major feature of the LLBLGen Pro Runtime Framework, our own ORM framework shipped with LLBLGen Pro, namely Resultset Caching.

I have written about caching in ORMs before, though that particular way of caching was about materialized objects, which is something else than Resultset Caching. Resultset Caching is a mechanism which caches the actual resultset used during the execution of a select query for a given duration. This means that if a given Linq / QuerySpec query is specified to have its resultset cached for e.g. 10 seconds, every execution of the exact same query with the same parameters will pull the results from the cache for the 10 seconds after the query was first executed on the database.



Resultset caching is done through cache objects which implement the caching of resultsets, either by delegating it to 3rd party servers like the .NET system cache or e.g. Memcached, or by doing the caching themselves like the built-in ResultsetCache class. Cache objects implement a common interface, IResultsetCache , and it's easy to implement a cache object class yourself. With the LLBLGen Pro Runtime Framework we ship the built-in ResultsetCache class and in the recently started LLBLGen Pro contrib library (github) we ship an implementation of the IResultsetCache interface for caching data in the .NET 4+ cache MemoryCache. As you can see with the MemoryCache supporting implementation, it's very easy to add a class which caches the data in your cache system of choice, e.g. Redis.

Registering a cache


Example of registering an instance of the ResultsetCache:

CacheController.RegisterCache(connectionString, new ResultsetCache());

After this line, all queries executed over a connection with the same connection string and which are specified to have their resultsets cached, are cached in the ResultsetCache instance registered. This means you can have multiple caches if you have multiple databases in use at the same time. You can also use one cache for all databases in use, by specifying an empty string as the connection string.

What makes a query unique? The Cache Key

Resultsets are cached in a cache under a key, a CacheKey instance. A CacheKey is created from a query object which provides the values for the key.

A CacheKey uses:

  • The SQL statement
  • All parameter values before execution
  • Flags for in-memory distinct/limiting/paging


Specifying a query to have its resultset cached

LLBLGen Pro has three ways to specify a query: Linq, QuerySpec and the Low-level API. We primarily added caching specification code to Linq and QuerySpec, as those two query APIs are the recommended way to formulate queries. I'll show how to specify caching directives in Linq and QuerySpec below.

// typed list fetch 
var tl = new OrderCustomerTypedList();
var q = tl.GetQuerySpecQuery(new QueryFactory())
                 .CacheResultset(10);  // cache for 10 seconds
new DataAccessAdapter().FetchAsDataTable(q, tl);

// typed view fetch
var qf = new QueryFactory();
var q = qf.Create()
            .Select(InvoicesFields.CustomerId, InvoicesFields.CustomerName, InvoicesFields.OrderId)
            .CacheResultset(10);        // cache for 10 seconds
var tv = new InvoicesTypedView();
new DataAccessAdapter().FetchAsDataTable(q, tv);

// dynamic list / custom projection
var qf = new QueryFactory();
var q = qf.Employee
                   .On(EmployeeFields.EmployeeId == OrderFields.EmployeeId))
        .Select(() => new
                EmployeeId = EmployeeFields.EmployeeId.ToValue<int>(),
                Notes = EmployeeFields.Notes.ToValue<string>()
        .CacheResultset(5);    // cache for 5 seconds

// custom projection with nested set
var qf = new QueryFactory();
var q = qf.Create()
        .Select(() => new
                Key = CustomerFields.Country.ToValue<string>(),
                CustomersInCountry = qf.Customer.TargetAs("C")
                                         .CorrelatedOver(CustomerFields.Country.Source("C") ==

// entity fetch
var qf = new QueryFactory();
var q = qf.Customer
            .Where(CustomerFields.Country == "Germany")
// custom projection, with nested set
var metaData = new LinqMetaData(new DataAccessAdapter());
var q = (from c in metaData.Customer
          group c by c.Country into g
          select new
             CustomersInCountry = (
                  from c2 in metaData.Customer
                  where g.Key == c2.Country
                  select c2)

// entity fetch with prefetch path
LinqMetaData metaData = new LinqMetaData(adapter);
var q = (from c in metaData.Customer
          where c.Country == "Germany"
                 .Prefetch<OrderEntity>(c => c.Orders)
                     .SubPath(opath => opath
                         .Prefetch(o => o.OrderDetails)

As you can see, it's just an operator to the query, and from then on its resultset is cached in the cache object registered for the connection string used at runtime. If there's already a resultset, that one is used; if there's no cached resultset found, the one read from the database is cached for the specified amount of time so the next execution of the same query will re-use that resultset.

an easy way to tweak your data-access code on a per-query basis.


1 Comment

Comments have been disabled for this content.