Net Core: Entity Framework and SQL Server Temporal Tables, Automatic Scaffolding

Question

We are having issues using Entity Framework Core 2.2 with SQL Server temporal SystemVersioningTables.

The following resolve issues with Entity Framework and system versioning columns. entityframework core and sql 2016 temporal tables

Using the solution, is there a way for Entity Framework Core 2.2 to automatically add DatabaseGeneratedOption.Computed or OnModelCreating on SystemVersioning columns?

Is there a command parameter in dotnet ef dbcontext scaffold ?

We are seeking a way to automatically add this in, during automatic database scaffolding. This way, we don't have to manually add this in for all our 1000+ tables, or anytime we add new ones.

Company has many databases.

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

or

public partial class DatabaseDBContext : DbContext
{
    partial void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Table1>(entity =>
        {
            entity.Property(e => e.StartTime)
                .ValueGeneratedOnAddOrUpdate();
            entity.Property(e => e.EndTime)
                .ValueGeneratedOnAddOrUpdate();
        });
    }
}

We're using .NET Core 2.2.

Note: we do not want to hide the SQL Server columns, given as third solution in article.

David Browne - Microsoft · Accepted Answer · 2020-06-15T02:14:00.873

3

In OnModelCreating you can examine and modify the model. So this is as easy as:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    foreach (var et in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var prop in et.GetProperties())
        {
            if (prop.Name == "StartTime"|| prop.Name == "EndTime")
            {
                prop.ValueGenerated = Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAddOrUpdate;
            }
        }
    }
}

This is for EF Core 3.1. EF Core 2.2 is out of support. 2.1 is an LTS branch, as is 3.1. See https://devblogs.microsoft.com/dotnet/net-core-2-2-will-reach-end-of-life-on-december-23-2019/

edited Jun 15 '20 at 02:14

answered Jun 15 '20 at 02:04

David Browne - Microsoft

80,331
6
39
67

that's great, unfortunately I am using EF 2.2, you said 2.1 is an LTS Branch? do you think they will bring that functionality to 2.2 eventually with LTS? – Jun 15 '20 at 03:52
2.2 is gets no fixes or enhancements. You can do the same thing in 2.2. The syntax might just be a bit different. – David Browne - Microsoft Jun 15 '20 at 03:55
hi David, maybe you can answer this question? https://stackoverflow.com/questions/62445608/c-entity-framework-fromsql-work-better-with-views-or-stored-procedures – Jun 18 '20 at 08:32
@david-browne-microsoft Why is it `ValueGenerated.OnAddOrUpdate` and not `ValueGenerated.OnAdd` ? Or would both do the same? – Simon Dec 19 '20 at 10:39

score 2 · Answer 2 · answered Nov 18 '21 at 09:56

.NET 6 and Entity Framework Core 6.0 supports SQL Server temporal tables out of the box.

Usage:

public class Customer
{
    public Guid Id { get; set; }
    public string Name  { get; set; }

    public List<Order> Orders { get; set; }
}

public class Order
{
    public Guid Id { get; set; }
    public DateTime OrderDate { get; set; }

    public Product Product { get; set; }
    public Customer Customer { get; set; }
}

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

using IsTemporal:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Customer>()
        .ToTable("Customers", b => b.IsTemporal());

    modelBuilder
        .Entity<Product>()
        .ToTable("Products", b => b.IsTemporal());

    modelBuilder
        .Entity<Order>()
        .ToTable("Orders", b => b.IsTemporal());
}

Querying historical data:

var productSnapshots = context.Products
    .TemporalBetween(from, to)
    .OrderBy(product => EF.Property<DateTime>(product, "PeriodStart"))
    .Where(product => product.Name == productName)
    .Select(product =>
        new
        {
            Product = product,
            PeriodStart = EF.Property<DateTime>(product, "PeriodStart"),
            PeriodEnd = EF.Property<DateTime>(product, "PeriodEnd")
        })
    .ToList();

Finding a specific historical record

var order = context.Orders
    .TemporalAsOf(on)
    .Include(e => e.Product)
    .Include(e => e.Customer)
    .Single(order =>
        order.Customer.Name == customerName
        && order.OrderDate > on.Date
        && order.OrderDate < on.Date.AddDays(1));

Restoring deleted data

var customerDeletedOn = context.Customers
    .TemporalAll()
    .Where(customer => customer.Name == customerName)
    .OrderBy(customer => EF.Property<DateTime>(customer, "PeriodEnd"))
    .Select(customer => EF.Property<DateTime>(customer, "PeriodEnd"))
    .Last();

var customerAndOrders = context.Customers
    .TemporalAsOf(customerDeletedOn.AddMilliseconds(-1))
    .Include(e => e.Orders)
    .Single();

context.Add(customerAndOrders);
context.SaveChanges();

Sources:

https://devblogs.microsoft.com/dotnet/prime-your-flux-capacitor-sql-server-temporal-tables-in-ef-core-6-0/

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/plan

https://github.com/dotnet/efcore/issues/4693