3 minutes to read read

Microsoft SQL Server brought some major enhancements recently one of them was native support for Temporal Design “out of the box”. Temporal Design can sometime be challenging. I have personally spent months writing a Custom Middleware layer for Temporal Support since client had explicit requirements for auditability. And believe me, it was not easy.

Realizing the potential of the feature, soon after Entity Framework Team jump into the bandwagon and we had a fully functional Temporal Design purely “as a code” that can be used directly. Also, the features include querying point in time (and other similar operations) out of the box which are very handy. Some of the major advantages are pointed out below.

In the post lets quickly have a look at the reference full implementation for temporal design, and also see one design problem that can come in your clean code development.

Major Advantages

1. Temporal Pattern supported as a code first model.
2. Since code first and entire snapshot is maintained in code, support complete version and change tracking across.
3. SInce it uses the `SYSTEM_VERSIONING` features i.e. Temporal Features of the database. This maintains the records to be audited and details be persisted in change tracking tables, even when the changes are committed outside of EF Core DAL Layer.
4. Simple Querying Model – The querying model for Point in Time or Spread in Time is most simplified using the Linq to SQL which provides most simplified and fluid dev experience.
5. Projection is supported on all the queries for perf oriented scenarios.
6. Entity Framework by default abstracts (hides) the time tracking fields. This can be projected on-demand.
7. Lastly, complete customization for Table and Column Name, straight from the code first approach.

Barebone Implementation / Reference Code base

Barebone but fully functional reference implementation and sample for Temporal Implementation along with all the instructions can be find here. We shall be discussing this in detail in a separate video, however, lets touch some major focus areas.

Defining the Tables as Temporal

Tables can be defined as Temporal with just one line of code as defined below

modelBuilder.Entity<User>().ToTable("Users", b => b.IsTemporal());

This simple line does all the magic in the background and enables versioning. The creation can be fully customized with the other extension methods.

modelBuilder.Entity<User>().ToTable("Users", b => b.IsTemporal(builder =>
{
    builder.HasPeriodStart("PeriodStart");
    builder.HasPeriodEnd("PeriodEnd");
    builder.UseHistoryTable("UserChanges");
}));

Thats it. Nothing else needed. The querying can be done using the Temporal Methods. Refer the Controller for sample implementations.

We shall be focusing on one design problem that arises out of the given implementation

The Normal Design

One of the most commonly used design for using Entity Framework is defined as below.

The lowest layers contains the plain POCO classes and then the DAL layers contains the configuration, DbContext. These layers are agnostic of the underlying DbEngine which is defined in the Migrations Assembly where the Database Configuration is also defined. This is followed by the business and Execution project.

The following design is discussed in details with a modal “bootstrapping” implementation defined here.

The .IsTemporal() extension methods are available in the package Microsoft.EntityFrameworkCore.SqlServer. Now this causes the problem becuase the configuration and the DbContext are defined in Dal Layer, which does not know of the Sql package. This causes methods to not resolve and fail harshly.

Solution

This problem is solved in very simple but effective and cleaner way. In the contracts or Dal library we define an interface that provide methods that can be overridden in runtime

namespace HRMS.Dal
{
    public interface IDbSpecificConfigurationProvider
    {
        void ConfigureDatabaseDependentExtensions(ModelBuilder modelBuilder);
    }
}

The system provide Noop Implementation which can be called in case non-sql database is selected. This can be registered by default or explicitly by calling services.RegisterNoopDbSpecificProvider(). This provide more controlled database abstraction and extension possibilities.

In the Migrations (or can be in any higher assembly) the interface can be implemented. Refer to the reference implementation. The class is internal on purpose and is registered in DI using RegisterMsSqlDbSpecificProvider() extension method.

The complete code base along with all the instructions to execute locally is available at our github repository. This code can be used to bootstrap or start a new project.