Entity Framework Core and SQL Server 2016 temporal tables

Question

We are using EF Core and SQL Server 2016 for our .NET Core Web API. I am evaluating use of temporal tables and its impact on EF Core code. When I generate the EF model using cmdline then it generates model with appstart, append and mappings in dbcontext. When I do insert/update they fail saying these columns cannot be updated. I had to remove appstart, end from model and dbcontext mapping to make it work. I read there is no interception capability yet in EF Core like EF 6.x.

Please advise about any better solutions for this.

score 5 · Accepted Answer · edited Dec 27 '18 at 04:22

I tried below options and they are working.

option 1: removed appstart, append fields from entities and dbcontext mappings and updates/insert started working properly.
option 2: decorate period columns with attributes as below.

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime StartTime { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime EndTime { get; set; }

score 3 · Answer 2 · edited Dec 27 '18 at 04:24

3

There currently is no better solution for this, and the feature is on the backlog.

edited Dec 27 '18 at 04:24

Shimmy Weitzhandler

97,705
120
409
613

answered Mar 06 '17 at 14:25

ErikEJ

38,654
5
69
109

Any solution to bypass the 'GENERATED ALWAYS' columns during INSERT/UPDATE in database first approach. ? I generate models using DBScaffold command and I cannot use the solution that is marked as answer here as everything gets overwritten each time I generate the model from database. https://stackoverflow.com/questions/52654609/error-when-inserting-into-temporal-table-using-entity-framework-core-2-1 – VPP Oct 11 '18 at 15:54
this should be as a comment, no as a solution, in fact there is a solution – Jun 14 '20 at 22:52

score 1 · Answer 3 · edited Nov 16 '18 at 11:17

1

Making the Period start column(StartTime) and Period end column(EndTime) hidden should fix this issue. We can do this by

ALTER TABLE [dbo].[Table1] ALTER COLUMN [StartTime] ADD HIDDEN;
ALTER TABLE [dbo].[Table1] ALTER COLUMN [EndTime] ADD HIDDEN;

We can see the settings for hidden against these columns in the sys.columns table

SELECT * FROM sys.columns WHERE is_hidden = 1

edited Nov 16 '18 at 11:17

Drummad

712
1
7
20

answered Nov 08 '18 at 20:40

VPP

665
1
8
30

score 0 · Answer 4 · answered Apr 08 '20 at 15:51

I think there's a better solution for this as follows: Create partial context file (to prevent re-making the changes after re-generating the model) as follows

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

score 0 · Answer 5 · answered Nov 18 '21 at 09:59

0

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

See this answer for examples:

https://stackoverflow.com/a/70017768/3850405

answered Nov 18 '21 at 09:59

Ogglas

50,115
30
272
333

Entity Framework Core and SQL Server 2016 temporal tables

5 Answers5

Linked