Insert Record in Temporal Table using C# Entity Framework

c# entity-framework entity-framework-6 sql-server temporal-database

Accepted Answer

The issue arises when EF tries to update values within.PERIOD system versioning column where SQL Server controls the values of the column properties.

The temporal table operates as a pair of current table and historical table from zzz-15 to zzz, and is stated as follows:

System-versioning for a table is implemented as a pair of tables, a current table and a history table. Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.

Period end column: The system records the end time for the row in this column, typically denoted at the SysEndTime column.

As bothStartTime & EndTime Any attempt to insert or change data on columns that are automatically produced must be rejected. If you're using EF 6, follow these steps to fix the error:

  1. In designer mode, open the EDMX file and set bothStartTime & EndTime column attributes asIdentity in StoreGeneratedPattern option. This stops EF from updating values on anyUPDATE events.

Identity Column Setting

  1. Make a class for the command tree interceptor that is unique and implementsSystem.Data.Entity.Infrastructure.Interception.IDbCommandTreeInterceptor and list the set clauses that should be marked as ReadOnlyCollection<T> (T is a DbModificationClause) and that EF should not be able to modify in insert or update modifications:

    internal class TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor
    {
        private static ReadOnlyCollection<DbModificationClause> GenerateSetClauses(IList<DbModificationClause> modificationClauses)
        {
            var props = new List<DbModificationClause>(modificationClauses);
            props = props.Where(_ => !_ignoredColumns.Contains((((_ as DbSetClause)?.Property as DbPropertyExpression)?.Property as EdmProperty)?.Name)).ToList();
    
            var newSetClauses = new ReadOnlyCollection<DbModificationClause>(props);
            return newSetClauses;
        }
    }
    
  2. Still using the same class above, the function should look like this (credits go to Matt Ruwe for this approach): Create a list of disregarded table names and specify actions in INSERT and UPDATE commands.

    // from /a/40742144
    private static readonly List<string> _ignoredColumns = new List<string> { "StartTime", "EndTime" };
    
    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;
            }
        }
    }
    
  3. Before accessing database context in another code portion, register the interceptor class described above by either usingDbInterception :

    DbInterception.Add(new TemporalTableCommandTreeInterceptor());
    

    may add it to the definition in context by usingDbConfigurationTypeAttribute :

    public class CustomDbConfiguration : DbConfiguration
    {
        public CustomDbConfiguration()
        {
            this.AddInterceptor(new TemporalTableCommandTreeInterceptor());
        }
    }
    
    // from /a/40302086
    [DbConfigurationType(typeof(CustomDbConfiguration))]
    public partial class DataContext : System.Data.Entity.DbContext
    {
        public DataContext(string nameOrConnectionString) : base(nameOrConnectionString)
        {
            // other stuff or leave this blank
        }
    }
    

Related concerns

With a temporal table, Entity Framework is incompatible

Using IDbCommandInterceptor to get the DbContext

only once IDbInterceptor is hooked up to EntityFramework DbContext

6
5/30/2017 7:22:51 AM

Popular Answer

The most straightforward approach would probably be to manually modify the.EDMX file and eliminate any references to the StartTime and EndTime columns.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow