Entity Framework does not work with temporary table

Question

I use the framework of the first database entity 6. After some of the tables in my schema were temporary, I began to receive the following error when trying 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 that are managed by the system.

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

Answer 1

There are two solutions to this problem:

1. In the properties window for the column in the EDMX constructor, replace StoreGeneratedPattern on the PERIOD columns (in my case ValidFrom and ValidTo) with identity . A certificate is better than a calculated one, since a computed one will force EF to update the values ​​in Insert and Update, and not just in the insert with identity
2. Create an implementation of IDbCommandTreeInterceptor to remove period columns. This is my preferred solution, as it does not require additional work when adding new tables to the model.

Here is 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 doing the following anywhere in your code before using context:

 DbInterception.Add(new TemporalTableCommandTreeInterceptor()); 

Answer 2

Another solution is to create a 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 

Nothing needs to be changed in the code.

Answer 3

Creating a period start column (ValidFrom) and a period end column (ValidTo) should solve this problem. We can do it

 ALTER TABLE [dbo].[Table1] ALTER COLUMN [ValidFrom] ADD HIDDEN; ALTER TABLE [dbo].[Table1] ALTER COLUMN [ValidTo] ADD HIDDEN; 

We can see the settings hidden for these columns in the sys.columns table

 SELECT * FROM sys.columns WHERE is_hidden = 1 

Answer 4

I managed to use a temporary table with an entity structure without any overhead.

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

   Another solution is to create a default constraint in the fields of the table.

   • Here is a script to modify a table instead of creating a 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. As Matt Rouvet says, change column to identity in edmx

   In the properties window for the column in the EDMX constructor, change the StoreGeneratedPattern for the PERIOD columns (in my case, ValidFrom and ValidTo) so that they are identical. An identity is better than a computed one, since a computed one will force EF to update the values ​​in the Insert and Update, and not just in the insert with the identity

3. Since the two methods described above work great for insertion, they do not work for updating entities. I had to manually say that the two columns were not changed,

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

Now I can successfully call db.SaveChanges() and get rid of the error, even if the entities have been changed. Hope this helps! Note: I used DbFirst and EF6

Answer 5

I ran into this error in the system version of the table, and I just set the EF configuration to ignore system-supported columns, like so

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

and insert / update works with a database that updates these columns as needed to save history. Another way would be to adjust the column like this

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