EDennis.AspNetCore.Base introduces support for "temporal entities." In this context, temporal entities refer to entities for whom historical records are created and retrieved through entity framework operations. Temporal entities do not meet the SQL2011 standard for temporal databases and they do not provide performance benefits over built-in temporal features of enterprise RDBMS's; however, they do offer some advantages over the pure RDBMS handling of historical records.

Background

Oracle, IBM, and SQL Server have had support for temporal databases for many years. Both IBM and Oracle provide support for system period tables, application period tables, and bitemporal tables, while SQL Server provides support for system period tables only.

System Period Tables

System period tables have records whose valid time periods reflect when UPDATE and DELETE RDMBS operations take place. Prior to temporal database support, database developers often used UPDATE and DELETE (and sometime INSERT) triggers to produce system period records in history tables. Triggers were effective, but they required some additional development work. Also, in order to create "as-of" queries (queries that return records as of a particular point in time), developers had to write more complex queries, involving unions over current and historical tables. With built-in support for system period tables, the major RDBMS's make it relatively easy to setup automatic auditing of UPDATE and DELETE operations. In addition, "as-of" (and similar) queries can be written just by adding a few keywords to SQL.

Application Period Tables

Application period tables have records whose valid time periods are controlled by the applications and possibly the users themselves. The business case for application period tables is fairly straightforward. SQL UPDATE and DELETE operations typically do not occur at precisely the point in time when a fact (e.g., a person's address or a record's status) has changed. Often, there is a lag in time between the fact change and the update or delete of the record. Indeed, sometimes there database operations even precede an associated fact change (e.g., when a new rate takes effect). For those kinds of situations, system periods may not be sufficient. Oracle and IBM provide support for application periods in order to allow applications/users the ability to control the effective date ranges for records, when needed. These application periods can be queried with as of (and similar) expressions in the same way that system periods can be queried. Importantly SQL Server doesn't provide the same kind of support for application periods, the developer can create and manage application periods as regular columns.

Bitemporal Tables

Oracle and IBM have support for bitemporal tables -- tables whose records have both system periods and application periods. Indeed, Oracle has support for multi-temporal tables, where any number of periods (addressing a variety of business issues) can be added to records.

Temporal Entities

Temporal entities were designed to provide an application-controlled alternative to system period tables with limited support for as of queries. More specifically, temporal entities address the following business needs:

• The ability to control when history records are created in order to prevent bloat in history records when individual records are progressively built/saved.
• The ability to perform as of queries in a (relatively) natural way using Linq and Entity Framework operations.

General Setup

Temporal entities are configured with DbContext classes and Temporal Repo classes

DbContext Configuration

To support temporal entities, the developer creates two or three DbContext classes:

1. Current Records DbContext, which provides all of the configurations for current-record tables, including primary keys, table/schema mappings, foreign key mappings, and (optionally) support for test records.
2. History Records DbContext, which provides all of the configurations for history-record tables, including (expanded) primary keys, table/schema mappings, and (optionally) support for test records.
3. (Optional) Base DbContext, which provides configurations common to current-record tables and history-record tables (e.g., maximum size of string/varchar fields). Importantly: history tables should not define foreign key constraints. If your history model includes navigation properties, foreign key constraints may be generated automatically by conventions. To prevent these foreign key constraints from being generated in history tables, your history-records context can include a call to the IgnoreNavigationProperties() extension method provided by this library.

Temporal Repo Configuration

EDennis.AspNetCore.Base includes two base repository classes with support for temporal operations, WriteableTemporalRepo and ReadonlyTemporalRepo. Both repository classes provide the following temporal queries:

• QueryAsOf(DateTime asOf, ... ), which allows one to specify that a query should be evaluated for a specific point in time. This query method also provides support for paging and ordering.
• QueryAsOf(DateTime from, DateTime to, ... ), which allows one to specify that a query should be evaluated for a date range, returning all records (current and history) that were valid during the time frame indicated. This query method also provides support for paging and ordering.

The WriteableTemporalRepo class provides these additional methods (which need a primary key):

• GetByIdAsOf(DateTime asOf, params object[] key), which returns the record (current or historical), which was valid for the date/time indicated.
• GetByIdHistory(params object[] key), which returns both current and historical records for the date/time indicated.

Controlling When History Records Are Created

The WriteableTemporalRepo class includes two virtual methods, WriteUpdate and WriteDelete, that allow one to configure the rules that determine when a history record is created during update and delete operations, respectively. By default, history records are always created during update and delete operations; however, one can override the default quite easily. Below are examples of overriding the WriteUpdate and WriteDelete methods to write history records only when either the SysUser changes or at least eight hours have elapsed since the last operation for the record (insert or update).

public virtual bool WriteUpdate(TEntity next, TEntity current)
            => (DateTime.Now > current.SysStart.AddHours(8)
                || SysUser != current.SysUser);

public virtual bool WriteDelete(TEntity current)
            => (DateTime.Now > current.SysStart.AddHours(8)
                || SysUser != current.SysUser);

A useful approach is to identify one or more default/common values for your organization and create one or more base repositories that extend WriteableTemporalRepo and override the above methods.

Limitations

Temporal entities were not designed to provide optimal performance in creating or querying history records. In contrast, the temporal databases that enterprise RDBMS's generate and maintain are optimized for performance. If optimal performance is of prime concern, then consider using RDBMS temporal databases, instead. EDennis.MigrationsExtensions does provide tools for generating SQL Server temporal tables during Entity Framework migrations.

Temporal entities do not provide IQueryable extension methods. Such temporal extension methods could lead to more natural expression construction and possibly more optimal query execution. That said, producing extension methods with the current implementation of temporal entities (which uses two context classes and parallel, but distinct IQueryables) would be quite challenging.