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.

Pittance

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());

Pittance

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

@AramGevorgyan - You can use the attribute [DatabaseGenerated(DatabaseGeneratedOption.Computed)] on the properties or use the Fluent API method .ValueGeneratedOnAddOrUpdate() e.g. entity.Property(e => e.ValidFrom).ValueGeneratedOnAddOrUpdate(); see here for reference. – Ramekin

Worked like a charm! The usings are as follow using System.Data.Entity.Infrastructure.Interception; using System.Data.Entity.Core.Common.CommandTrees; using System.Data.Entity.Core.Metadata.Edm; using System.Collections.ObjectModel; – Thiosinamine

"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" - uhm, but that's the point: the values for the period columns will be updated on every modification, so EF should keep them updated, no? – Borszcz

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

Madelle

Perfect solution for me! Works perfectly. – Sneakbox

Would [DatabaseGenerated(DatabaseGeneratedOption.Computed)] public DateTime? SysStartTime { get; set; } in the datamodel have the same effect? See learn.microsoft.com/en-us/ef/core/modeling/… – Devonadevondra

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.

Millstone

created this table but inserting with db.Tables.Add(new Table() {Description = "des", Id = 1}); still gives error Cannot insert an explicit value into... – Clubby

Even with the default constraint? – Commitment

Well, I just posted my answer, it was working for inserting, but not updating. Your answer was part of my solution, thanks! – Jap

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

Jap

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

Communalize