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.

Emikoemil

Answer 1

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/

Krystenkrystin

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? – Emikoemil

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. – Krystenkrystin

hi David, maybe you can answer this question? #62446108 – Emikoemil

@david-browne-microsoft Why is it ValueGenerated.OnAddOrUpdate and not ValueGenerated.OnAdd ? Or would both do the same? – Tso

Answer 2

.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

Easterner