Insert Record in Temporal Table using C# Entity Framework

Question

I'm having issue in Insertion of data in Temporal table using C# Entity Framework

The Table schema is

CREATE TABLE People( 
    PeopleID int PRIMARY KEY NOT NULL, 
    Name varchar(50) Null, 
    LastName varchar(100) NULL, 
    NickName varchar(25), 
    StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL, 
    PERIOD FOR SYSTEM_TIME (StartTime,EndTime) 
) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

I created an EDMX asusal and I tried to Insert a record using following C# Code

using (var db = new DevDBEntities()) {
    People1 peo = new People1() {
        PeopleID = 1,
        Name = "Emma",
        LastName = "Watson",
        NickName = "ICE"
    };

    db.Peoples.Add(peo);
    db.SaveChanges();
}

I got an exception while on db.SaveChanges()

"Cannot insert an explicit value into a GENERATED ALWAYS column in table 'DevDB.dbo.People'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column."

I tried direct insertion using SQL Server using the following Insert Query, its inserting fine.

INSERT INTO [dbo].[People]
           ([PeopleID]
           ,[Name]
           ,[LastName]
           ,[NickName])
     VALUES
           (2
           ,'John'
           ,'Math'
           ,'COOL')

Kindly assist me how to insert an record using C# Entity Framework.

Valenevalenka

Try changing StoreGeneratedPattern for PeopleID to Identity or Computed (in case of GUID) at EDMX file and implement IDbCommandTreeInterceptor to remove primary key column insertion attempt in temporal table using string collection. – Assiut

@TetsuyaYamamoto - Its failing (i.e., throwing same exception) – Valenevalenka

My bad, I totally missed that both StartTime & EndTime are autogenerated columns with GENERATED ALWAYS- set StoreGeneratedPattern for those columns to Identity. Certainly you need to implement additional custom command tree interceptor implementing IDbCommandTreeInterceptor to handle those autogenerated columns, maybe requiring more explanation more than this comment. – Assiut

@TetsuyaYamamoto - Could you please assist me in Answer section with complete code. – Valenevalenka

Answer 1

Light summary: The problem occurs when EF trying to update values inside PERIOD system versioning column which the column property values are managed by SQL Server itself.

From MS Docs: Temporal tables, the temporal table works as a pair of current table and history table which explained as this:

System-versioning for a table is implemented as a pair of tables, a current table and a history table. Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.

Period end column: The system records the end time for the row in this column, typically denoted at the SysEndTime column.

As both StartTime & EndTime column are automatically generated, they must be excluded from any attempt to insert or update values on them. Here are these steps to get rid of the error, assuming you're in EF 6:

1. Open EDMX file in designer mode, set both StartTime & EndTime column properties as Identity in StoreGeneratedPattern option. This prevents EF refreshing values on any UPDATE events.

2. Create a custom command tree interceptor class which implements System.Data.Entity.Infrastructure.Interception.IDbCommandTreeInterceptor and specify set clauses which should be set as ReadOnlyCollection<T> (T is a DbModificationClause) which cannot be modified by EF in insert or update modifications:

   internal class TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor
   {
       private static ReadOnlyCollection<DbModificationClause> GenerateSetClauses(IList<DbModificationClause> modificationClauses)
       {
           var props = new List<DbModificationClause>(modificationClauses);
           props = props.Where(_ => !_ignoredColumns.Contains((((_ as DbSetClause)?.Property as DbPropertyExpression)?.Property as EdmProperty)?.Name)).ToList();
   
           var newSetClauses = new ReadOnlyCollection<DbModificationClause>(props);
           return newSetClauses;
       }
   }
   

3. Still in the same class above, create list of ignored table names and define actions in INSERT and UPDATE commands, the method should be looks like this (credits to Matt Ruwe for this method):

   // from /a/40742144
   private static readonly List<string> _ignoredColumns = new List<string> { "StartTime", "EndTime" };
   
   public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
   {
       if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
       {
           var insertCommand = interceptionContext.Result as DbInsertCommandTree;
           if (insertCommand != null)
           {
               var newSetClauses = GenerateSetClauses(insertCommand.SetClauses);
   
               var newCommand = new DbInsertCommandTree(
                   insertCommand.MetadataWorkspace,
                   insertCommand.DataSpace,
                   insertCommand.Target,
                   newSetClauses,
                   insertCommand.Returning);
   
               interceptionContext.Result = newCommand;
           }
   
           var updateCommand = interceptionContext.Result as DbUpdateCommandTree;
           if (updateCommand != null)
           {
               var newSetClauses = GenerateSetClauses(updateCommand.SetClauses);
   
               var newCommand = new DbUpdateCommandTree(
               updateCommand.MetadataWorkspace,
               updateCommand.DataSpace,
               updateCommand.Target,
               updateCommand.Predicate,
               newSetClauses,
               updateCommand.Returning);
   
               interceptionContext.Result = newCommand;
           }
       }
   }
   

4. Register the interceptor class above before database context usage in another code part either by using DbInterception:

   DbInterception.Add(new TemporalTableCommandTreeInterceptor());
   

   or attach it in context definition using DbConfigurationTypeAttribute:

   public class CustomDbConfiguration : DbConfiguration
   {
       public CustomDbConfiguration()
       {
           this.AddInterceptor(new TemporalTableCommandTreeInterceptor());
       }
   }
   
   // from /a/40302086
   [DbConfigurationType(typeof(CustomDbConfiguration))]
   public partial class DataContext : System.Data.Entity.DbContext
   {
       public DataContext(string nameOrConnectionString) : base(nameOrConnectionString)
       {
           // other stuff or leave this blank
       }
   }
   

Related issues:

Entity Framework not working with temporal table

Getting DbContext from implementation of IDbCommandInterceptor

Hooking IDbInterceptor to EntityFramework DbContext only once

Assiut

How can I do the same thing with entity framework core? – Adora

Answer 2

Probably the simplest solution would be manually edit the .EDMX file and remove all traces of the StartTime and EndTime columns.

Warrigal