The normalized data model of the database is often not suitable for reading and displaying data. A separate read model used to represent all the data needed to display a page improves performance.

Defining the read model is only half the work though, to make it really usable the read model should accept queries in the same way as the write model (the DB Entities) does. In .NET/C# that means that the read model should implement IQueryable<T> to enable it to be queried with LINQ. By building the read model on top of the write model it becomes a breeze.

from c in ctx.Cars.SelectCarReadModel()
where c.Car.CarId == id
select c

A call to an extension method is all that’s needed – the query above produces a CarReadModel result, containing the additional data needed for displaying that’s not directly part of the Car entity.

The SelectCarReadModel Extension Method

In the query above, the SelectCarReadModel extension method is called to transform the ctx.Cars entity set (database table) into a virtual entity set of type CarReadModel. It works very much like a database view, except that this is done in LINQ instead of in the database. The extension method itself is quite small and does the conversion from IQueryable<Car> into IQueryable<CarReadModel>.

public static IQueryable<CarReadModel> SelectCarReadModel(this IQueryable<Car> query)
{
    return query.Select(c => new CarReadModel
    {
        Car = c,
        BrandName = c.Brand.Name
    });
}

When to Call SelectCarReadModel

The SelectCarReadModel works on IQueryable<Car> instances, like the one produced by a normal query against ctx.Cars. Looking at last week’s code sample…

from c in ctx.Cars
where c.CarId == id
select new CarReadModel
{
    Car = c,
    BrandName = c.Brand.Name
}

… we can break out the select part …

(from c in ctx.Cars
 where c.CarId == id
 select c).SelectCarReadModel()

… or move the projection (projection is just a nice word for calling Select) to get cleaner code …

from c in ctx.Cars.SelectCarReadModel()
where c.Car.CarId == id
select c

… and enabling queries directly against the read model’s properites …

from c in ctx.Cars.SelectCarReadModel()
where c.BrandName.StartsWith(brandFilter)
select c

Query Performance

The objective of separating the read model in the first place was to get better performance. All the queries above will result in only one request to the database (as long as no lazy loaded navigation properties of the included entities are utilized). The generated SQL is somewhat more complex when doing the projection (calling Select) at the end, than when the projection is done directly with ctx.Cars.SelectCarReadModel(). The actual query execution plans are identical for all three queries filtering by id. The last query (by id) generates the most simple SQL query, which looks very much like if I had written it manually.

SELECT TOP (2) 
[Extent1].[CarId] AS [CarId], 
[Extent1].[BrandId] AS [BrandId], 
[Extent1].[RegistrationNumber] AS [RegistrationNumber], 
[Extent1].[TopSpeed] AS [TopSpeed], 
[Extent1].[Color] AS [Color], 
[Extent2].[Name] AS [Name]
FROM  [dbo].[Cars] AS [Extent1]
INNER JOIN [dbo].[Brands] AS [Extent2] ON [Extent1].[BrandId] = [Extent2].[BrandId]
WHERE [Extent1].[CarId] = 4

DRY and Avoiding Multiple Database Calls

By separating the read model from the write model multiple database calls are avoided. The number of queries required for one user interaction is the number one factor that determines performance for any application using databases. There are of course other factors too, such as indexing, but no index can ever save the day if the application makes too many database calls. It’s better to give the DB engine an as complete view of the data required as possible. Then it’s up the the DB (and the DBA tuning the DB) to make sure that there are suitable indexes.

The greatest threat against good DB queries is lazy programmers. If it is too hard to write queries that exactly match what’s needed, it’s often easier to use an existing query – although it returns too much. With LINQ and extension methods like SelectCarReadModel there is only a minimal amount of extra coding required, in one single place (DRY!) to ensure that each query is optimized to return only the relevant data.