Entity Framework not working with temporal table

问题:

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.

回答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 either identity or computed. Identity is probably better 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());

回答2:

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.

回答3:

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

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