We have recently upgraded our project to Microsoft.EntityFrameworkCore 6.0.0. This release enables SQL Server temporal tables out of the box.
https://stackoverflow.com/a/70017768/3850405
We have used temporal tables since Entity Framework Core 3.1 using custom migrations as described here:
https://stackoverflow.com/a/64776658/3850405
https://stackoverflow.com/a/64244548/3850405
Simply following Microsofts guide will of course not work since default column names are PeriodStart and PeriodEnd instead of our SysStartTime and SysEndTime. History table name does not match either.
modelBuilder
.Entity<Comment>()
.ToTable("Comments", b => b.IsTemporal());
Migration created:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AlterTable(
name: "Comments")
.Annotation("SqlServer:IsTemporal", true)
.Annotation("SqlServer:TemporalHistoryTableName", "CommentsHistory")
.Annotation("SqlServer:TemporalHistoryTableSchema", null)
.Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
.Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
migrationBuilder.AddColumn<DateTime>(
name: "PeriodEnd",
table: "Comments",
type: "datetime2",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified))
.Annotation("SqlServer:IsTemporal", true)
.Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
.Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
migrationBuilder.AddColumn<DateTime>(
name: "PeriodStart",
table: "Comments",
type: "datetime2",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified))
.Annotation("SqlServer:IsTemporal", true)
.Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
.Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
}
Creating a custom conversion should fix this as described below:
modelBuilder
.Entity<Comment>()
.ToTable("Comments", tb => tb.IsTemporal(temp =>
{
temp.UseHistoryTable("Comments", "History");
temp.HasPeriodStart("SysStartTime");
temp.HasPeriodEnd("SysEndTime");
}));
However when doing it like this I get the following error on Add-Migration command:
Period property 'Comment.SysStartTime' must be a shadow property.
To verify there was nothing wrong with any other code I had I reverted to:
modelBuilder
.Entity<Comment>()
.ToTable("Comments", b => b.IsTemporal());
And then added public DateTime PeriodStart { get; set; } to Comment.
I then received the error:
Period property 'Comment.PeriodStart' must be a shadow property.
Is there any way to get around this? We use our SysStartTime as a last modified/last updated value and it works really well. Having to include it via EF.Property<DateTime>(comment, "SysStartTime")) seems very unnecessary since the column is present both in temporal table and the original table.
Not possible to fix in EF Core 6.0.
From @ajcvickers, Engineering manager for Entity Framework:
Unfortunately, there isn't any workaround for this that allows both the use of the new temporal table features, and mapping the period columns to non-shadow properties.
https://github.com/dotnet/efcore/issues/26960#issuecomment-991867756
Vote below if you want to see this feature in EF Core 7.0:
https://github.com/dotnet/efcore/issues/26463
For new entities using SQL Server temporal tables with EF Core we do use IsTemporal() so that we can use the built in support for querying historical data.
However instead of specifying a table name like this ToTable("Customers"):
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<Customer>()
.ToTable("Customers", b => b.IsTemporal());
}
We simply set it like this:
modelBuilder.Entity<Customer>().ToTable(tb => tb.IsTemporal());
In order to not bloat OnModelCreating if you have a lot of entities a private method can be used like this:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
SetTemportalTables(modelBuilder);
}
private void SetTemportalTables(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Customer>().ToTable(tb => tb.IsTemporal());
modelBuilder.Entity<Product>().ToTable(tb => tb.IsTemporal());
modelBuilder.Entity<Orders>().ToTable(tb => tb.IsTemporal());
}
If you want every entity that implements an interface like IEntity to have SQL Server temporal tables we solved it like this:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
foreach (var property in modelBuilder.Model.GetEntityTypes())
{
if (property.ClrType.IsAssignableTo(typeof(IEntity)))
{
property.SetIsTemporal(true);
}
}
}