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.

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. — Tetsuya Yamamoto, May 30 '17 at 05:54
@TetsuyaYamamoto - Its failing (i.e., throwing same exception) — , May 30 '17 at 06:08
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. — Tetsuya Yamamoto, May 30 '17 at 06:21
@TetsuyaYamamoto - Could you please assist me in Answer section with complete code. — , May 30 '17 at 06:23

score 8 · Accepted Answer · answered May 30 '17 at 07:22

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:

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.

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;
    }
}

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;
        }
    }
}

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

How can I do the same thing with entity framework core? – Aram Gevorgyan Oct 02 '17 at 19:25 — Aram Gevorgyan, Oct 02 '17 at 19:25

score 1 · Answer 2 · answered Mar 16 '20 at 16:00

1

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

answered Mar 16 '20 at 16:00

Phi Nguyen

11
2

Insert Record in Temporal Table using C# Entity Framework

2 Answers2

Linked