EntityFrameworkCore.SqlServer.TemporalTable
1.2.4
See the version list below for details.
dotnet add package EntityFrameworkCore.SqlServer.TemporalTable --version 1.2.4
NuGet\Install-Package EntityFrameworkCore.SqlServer.TemporalTable -Version 1.2.4
<PackageReference Include="EntityFrameworkCore.SqlServer.TemporalTable" Version="1.2.4" />
paket add EntityFrameworkCore.SqlServer.TemporalTable --version 1.2.4
#r "nuget: EntityFrameworkCore.SqlServer.TemporalTable, 1.2.4"
// Install EntityFrameworkCore.SqlServer.TemporalTable as a Cake Addin
#addin nuget:?package=EntityFrameworkCore.SqlServer.TemporalTable&version=1.2.4
// Install EntityFrameworkCore.SqlServer.TemporalTable as a Cake Tool
#tool nuget:?package=EntityFrameworkCore.SqlServer.TemporalTable&version=1.2.4
Setup
- Add the following class in Startup project.
class EnhancedDesignTimeServices : IDesignTimeServices
{
public void ConfigureDesignTimeServices(IServiceCollection services)
{
services.AddSingleton<ICSharpMigrationOperationGenerator, TemporalCSharpMigrationOperationGenerator>();
}
}
Note: Please comment the following section in your csproj file in order to make the IDesignTimeServices
visible in your code.
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.0">
</PackageReference>
- Use the following method to configure DbContext. UseInternalServiceProvider need to be called to let DbContext resolve depencdencies with the provider we have configure.
IServiceCollection services = new ServiceCollection();
services.AddDbContext<TemporalTestDbContext>((provider, options) =>
{
options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"));
options.UseInternalServiceProvider(provider);
});
services.AddEntityFrameworkSqlServer();
services.RegisterTemporalTablesForDatabase();
var provider = services.BuildServiceProvider();
return provider.GetService<TemporalTestDbContext>();
Entity Configuration
- Enable temporal table for entity
modelBuilder.Entity<User>(b =>
{
b.ToTable("Users");
b.HasTemporalTable();
});
- Enable temporal table with custom start/end date column name
modelBuilder.Entity<TransactionRecord>(b =>
{
b.HasTemporalTable(config =>
{
config.StartDateColumn("ValidFrom");
config.EndDateColumn("NewValidTo");
});
});
- Optional. You may configure the history table name
modelBuilder.Entity<User>(b =>
{
b.ToTable("Users");
b.HasTemporalTable(config =>
{
config.HistorySchema("history");
config.HistoryTable("UserHistories");
});
});
Temporal Query
Example:
modelBuilder.Entity<TransactionRecord>(b =>
{
b.HasTemporalTable(config =>
{
config.StartDateColumn("ValidFrom");
config.EndDateColumn("NewValidTo");
config.DataConsistencyCheck(true);
});
});
var transactions = context.Set<TransactionRecord>()
.FromTo(_InititalDate, _IncrementedDate)
.Select(t => new
{
Amount = t.Amount,
CreatedDate = t.CreatedDate,
SysStartDate = EF.Property<DateTime>(t, TemporalAnnotationNames.DefaultStartTime),
SysEndDate = EF.Property<DateTime>(t, TemporalAnnotationNames.DefaultEndTime)
});
Generated SQL:
DECLARE __p_0 datetime2 = '2021-01-26T11:04:36.3259831Z';
DECLARE __p_1 datetime2 = '2021-01-26T11:04:41.5200058Z';
SELECT [t].[Amount], [t].[CreatedDate], [t].[ValidFrom] AS [SysStartDate], [t].[NewValidTo] AS [SysEndDate]
FROM [TransactionRecord] FOR SYSTEM_TIME FROM @__p_0 TO @__p_1 AS [t]
This extention will create two shadow properties for the Start/End date column. The property name is defined in TemporalAnnotationNames
class.
Supported Temporal Queries:
- AS OF <date_time>
- FROM <start_date_time> TO <end_date_time>
- BETWEEN <start_date_time> AND <end_date_time>
- CONTAINED IN (<start_date_time> , <end_date_time>)
- ALL
Migrations
Here are the migrations generated by EntityFramework migration tool (Add-Migration, Script-Migration)
Supported migration operations
- Adding a new table with history tracking.
- Enable history tracking on existing table.
- Disable history tracking on existing temporal table.
- Support custom column name for "start/end" date column.
- Support custom schema and table name for history table.
- Support "start/end" date column rename.
- Support option for data consistency check.
Sample Migrations Generated by EntityFramework Tool
- Adding a new table with temporal feature.
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "TransactionRecord",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Amount = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
CreatedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
LastModifiedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
ValidTo = table.Column<DateTime>(type: "datetime2", nullable: false),
ValidFrom = table.Column<DateTime>(type: "datetime2", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_TransactionRecord", x => x.Id);
});
migrationBuilder.EnableTemporalTable(
table: "TransactionRecord",
historyTable: "TransactionRecordHistories",
startColumn: "ValidFrom",
endColumn: "ValidTo");
}
- Update existing table from non-temporal become temporal table
migrationBuilder.AddColumn<DateTime>(
name: "SysEndTime",
table: "Users",
type: "datetime2",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
migrationBuilder.AddColumn<DateTime>(
name: "SysStartTime",
table: "Users",
type: "datetime2",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
migrationBuilder.EnableTemporalTable(
table: "Users",
historyTable: "UsersHistories",
startColumn: "SysStartTime",
endColumn: "SysEndTime");
- Disable history tracking
migrationBuilder.DisableTemporalTable(
table: "Users");
migrationBuilder.DropColumn(
name: "DateFrom",
table: "Users");
migrationBuilder.DropColumn(
name: "SysEndTime",
table: "Users");
Please note that both the SysStart and SysEnd date column will be dropped by default.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.1 is compatible. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.1
- Microsoft.EntityFrameworkCore (>= 5.0.0)
- Microsoft.EntityFrameworkCore.Design (>= 5.0.0)
- Microsoft.EntityFrameworkCore.Relational (>= 5.0.0)
- Microsoft.EntityFrameworkCore.SqlServer (>= 5.0.0)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
2.0.1-Preview | 204 | 11/1/2021 |
2.0.0-Preview | 323 | 10/17/2021 |
1.2.4 | 11,879 | 6/11/2021 |
1.2.3 | 365 | 6/11/2021 |
1.2.2 | 1,679 | 2/16/2021 |
1.2.1 | 1,358 | 1/26/2021 |
1.2.0 | 418 | 1/22/2021 |
1.1.0 | 413 | 1/21/2021 |
1.0.0 | 419 | 1/21/2021 |
- fix wrong default end date column name being used when generating migration script.
- added test scenario to demonstrate entity inheritance with TPT (table per type) is able to use temporal table for each type.