Entity Framework not working with temporal table

Question

I'm using database first entity framework 6. After changing some of the tables in my schema to be temporal tables, I started getting the following error when attempting to insert new data:

Cannot insert an explicit value into a GENERATED ALWAYS column in table '<MyDatabase>.dbo.<MyTableName>. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

It looks like EF is trying to update the values of the PERIOD columns which are managed by the system.

Removing the columns from the EDMX file seems to correct the problem, but this is not a viable solution since the columns are re-added each time the model is regenerated from the database.

Answer 1

There are two solutions to this problem:

1. In the property window for the column in the EDMX designer, change the StoreGeneratedPattern on the PERIOD columns (ValidFrom and ValidTo in my case) to be identity. Identity is better than computed since computed will cause EF to refresh the values on an Insert and Update as opposed to just an insert with identity
2. Create an IDbCommandTreeInterceptor implementation to remove the period columns. This is my preferred solution since it requires no additional work when adding new tables to the model.

Here's my implementation:

using System.Data.Entity.Infrastructure.Interception; 
using System.Data.Entity.Core.Common.CommandTrees; 
using System.Data.Entity.Core.Metadata.Edm; 
using System.Collections.ObjectModel;

internal class TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor
{
    private static readonly List<string> _namesToIgnore = new List<string> { "ValidFrom", "ValidTo" };

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

    private static ReadOnlyCollection<DbModificationClause> GenerateSetClauses(IList<DbModificationClause> modificationClauses)
    {
        var props = new List<DbModificationClause>(modificationClauses);
        props = props.Where(_ => !_namesToIgnore.Contains((((_ as DbSetClause)?.Property as DbPropertyExpression)?.Property as EdmProperty)?.Name)).ToList();

        var newSetClauses = new ReadOnlyCollection<DbModificationClause>(props);
        return newSetClauses;
    }
}

Register this interceptor with EF by running the following anywhere in your code before you use your context:

DbInterception.Add(new TemporalTableCommandTreeInterceptor());

Answer 2

I've ran into this error on a system-versioned table and I just set the EF configuration to ignore the system maintained columns like so

            Ignore(x => x.SysEndTime);
            Ignore(x => x.SysStartTime);

and insert/update works with DB still updating these columns as necessary to keep history. Another way would be to setup the the column like so

Property(x => x.SysEndTime).IsRequired().HasColumnType("datetime2").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

Answer 3

An other solution is create default constraint in the fields of the table.

CREATE TABLE [dbo].[Table] (
    [Id]            INT IDENTITY(1, 1)  NOT NULL,
    [Description]   NVARCHAR(100)       NOT NULL,
    [ValidFrom]     DATETIME2(0)        GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT [Df_Table_ValidFrom] DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    [ValidTo]       DATETIME2(0)        GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT [Df_Table_ValidTo] DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),
    CONSTRAINT [Pk_Table] PRIMARY KEY CLUSTERED ([Id] ASC)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Table_History]));
GO

In the code not need alteration nothing.

Answer 4

I did manage to use temporal table with entities framework without any overhead.

1. Use the default constraint, as José Ricardo Garcia says

   An other solution is create default constraint in the fields of the table.

   • Heres the script for altering table instead of creating table.

     ALTER TABLE [dbo].[Table]
     ADD ValidFrom DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT [Df_Table_ValidFrom] DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
     ValidTo   DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT [Df_Table_ValidTo] DEFAULT '9999.12.31 23:59:59.99',
     PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
     go
     ALTER TABLE [dbo].[Table]
     SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.[TableHistory]))
     GO
     

2. Switch column to identity in edmx, as Matt Ruwe says

   In the property window for the column in the EDMX designer, change the StoreGeneratedPattern on the PERIOD columns (ValidFrom and ValidTo in my case) to be identity. Identity is better than computed since computed will cause EF to refresh the values on an Insert and Update as opposed to just an insert with identity

3. Since the two method above are working just fine for insert, they didnt work for updating the entities. I had to manually tell that the two column were not modified,

   Entry(existingResult).CurrentValues.SetValues(table);
   Entry(existingResult).Property(x => x.ValidTo).IsModified = false;
   Entry(existingResult).Property(x => x.ValidFrom).IsModified = false;
   

now i can succesffuly call db.SaveChanges() and get rid of the error, even if the entities has been modified. Hope it help! Note: I using DbFirst and EF6

Answer 5

Making the Period start column (ValidFrom) and Period end column (ValidTo) should fix this issue. We can do this by

ALTER TABLE [dbo].[Table1] ALTER COLUMN [ValidFrom] ADD HIDDEN;
ALTER TABLE [dbo].[Table1] ALTER COLUMN [ValidTo] 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