How to get ValidFrom and ValidTo columns from temporal tables in Entity Framework Core?

Question

Is there a way to get to ValidFrom and ValidTo columns in temporal tables in EFCore in C#?

This is how I initialized temporal table

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>().ToTable("Orders", options => 
            {
                options.IsTemporal(); 
            });
}

But I can't seem to access ValidFrom and ValidTo columns when using a query like this:

var history = context.Orders
    .TemporalAll()
    .Where(q => q.Id == orderId);

foreach (var item in history)
    Console.WriteLine($"{item.ValidFrom}{item.Name}");

Where in this row Console.WriteLine($"{item.ValidFrom}{item.Name}");
this part of the line item.ValidFrom shows an error saying that ValidFrom is not a column that exists.

I understand that these columns exist, just that they are hidden.

I will be using ValidFrom and ValidTo often in my code and prefer if they would not be hidden. Is there a way to do so?

Is it a good solution if I create an interface with ValidFrom and ValidTo properties just like the ones that are automatically created by EFCore temporial tables, and make my objects inherit from it?

score 3 · Accepted Answer · answered Jul 05 '22 at 15:07

The ValidFrom and ValidTo columns aren't part of your model, they are only available as shadow properties so if you want to read them, you need to use an anonymous type or, better, a different model class. You can Select into that class and use EF.Property to read the values. For example, have a new class that looks like this:

public class OrderHistory : Order // Inherit all values from the standard Order entity
{
    // I have a feeling these should actually be called PeriodStart and PeriodEnd...
    public DateTime ValidFrom { get; set; }
    public DateTime ValidTo { get; set; }
}

Now you could write a query like this:

var history = context.Orders
    .TemporalAll()
    .Where(o => o.Id == orderId)
    .Select(o => new OrderHistory
    {
        Id = o.Id,
        Name = o.Name,

        // Other Order properties

        ValidFrom = EF.Property<DateTime>(o, "ValidFrom"), 
        ValidTo = EF.Property<DateTime>(o, "ValidTo")
    });

How to get ValidFrom and ValidTo columns from temporal tables in Entity Framework Core?

1 Answers1