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.

  • Writing a csv file into SQL Server database using python
  • Connection String Error (error : 40)
  • Programmatically detach SQL Server database to copy mdf file
  • SQL Compact select top 1
  • What alternatives exist to using guid as clustered index on tables in SQL Azure (Federations)
  • How to cast computed column with correct decimal/$ result
  • 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.

  • SQL Server - inner join when updating
  • How do I create a list of 5 number permutations based on a list of numbers ranging from 1-69?
  • Insert PictureBox Image into Sql Server database
  • SQL Server restore and backup per schema
  • Set one row fields as a multiplication of 2 others
  • T-SQL - GROUP BY with LIKE - is this possible?
  • One Solution collect form web for “Entity Framework not working with temporal table”

    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:

    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());
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.