CQRS: Reporting Database Access Strategies

One of the things that I love about CQRS is that it completely eliminates the impedance mismatch that you normally experience with traditional ORMs. It is incredibly compelling to be able query and retrieve all values necessary for a particular report or screen in a single database call because of a denormalized database schema.

Over the past year there has been significant debate regarding Microsoft's LINQ to SQL, Entity Framework, and ADO.NET Data Services data access strategies. Further, NHibernate with the 2.0+ release has become the de-facto standard in many ALT.NET shops. Other solutions such as LLBLGen and Subsonic are also gaining more and more acceptance and traction.

With all of these options, the question becomes: which one should we use? The answer from a CQRS perspective is…it doesn't matter. That's right. It doesn't matter. One of the primary reasons this works is because the only place you need a relational database is in your reporting context and it follows the ActiveRecord pattern where the class structure mimics the table schema. Most data access mechanisms handle this scenario very, very well. But you've got to expose your data access through the correct abstraction.

In a project I am working on, we simply created an abstraction of our data access mechanism through an IAccessStorage interface which had a single property with the following signature:

IQueryable Items { get; }

It could be queried using either a "SQL-like" LINQ expression or like this:

storage.Items.Where(user => user.Id == 12345)

As a proof of concept, I created a class to abstract each persistence mechanism, e.g. NHibernateStorage, LinqToSqlStorage, EntityFrameworkStorage, and SubsonicStorage. Using dependency injection, I was easily able to swap out which storage mechanism I used and my application code was none the wiser.

The caveat in all of this is that each one must have a LINQ provider. While I've run into some quirks in NHibernate's LINQ provider related to "joins", this isn't really an issue for the reporting database because you don't really want to be doing joins anyway.

An additional issue that you may notice concerns the DTO/class files generated by the above technologies. This may be a small issue because the "User" object generated by Subsonic is not the same one generated LINQ to SQL or the Entity Framework. In the end, it isn't that big of a deal because these objects are simple DTOs with zero behavior. As long as they don't have behavior, you're okay, so you may end up with a dependency on the DTOs. If you really wanted to maintain purity, you could potentially use a solution like AutoMapper to map the generated objects to your own POCOs. [Update: The "dynamic" keyword in C# is another possible resolution to this.]


With all of the arguing that is happening regarding the various persistence mechanisms and their relative strengths and weaknesses, using CQRS allows us to completely avoid dependencies on any of the aforementioned projects thus allowing us to freely choose whichever one we see fit at that time. And if we make a mistake or discover additional capabilities in another ORM, we can swap implementations with virtually no effort. Thus, we are now doubly insulated from the database and we can swap both database and data access technologies at will.