使用 C# Entity Framework 在时态表中插入记录

Insert Record in Temporal Table using C# Entity Framework(使用 C# Entity Framework 在时态表中插入记录)

问题描述

我在使用 C# Entity Framework 插入 Temporal table 中的数据时遇到问题

表架构是

创建表人(PeopleID int PRIMARY KEY NOT NULL,名称 varchar(50) 空值，姓氏 varchar(100) NULL，昵称 varchar(25),StartTime datetime2 始终生成为 ROW START NOT NULL，EndTime datetime2 始终生成为 ROW END NOT NULL，PERIOD FOR SYSTEM_TIME(开始时间，结束时间)) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

我创建了一个 EDMX asusal，并尝试使用以下 C# 代码插入记录

使用 (var db = new DevDBEntities()) {人1人=新人1(){人 ID = 1，名称=艾玛"，姓氏 = "沃森",昵称 = "ICE"};db.Peoples.Add(peo);db.SaveChanges();}

我在 db.SaveChanges()

"不能在 GENERATED ALWAYS 列中插入显式值表'DevDB.dbo.People'.将 INSERT 与列列表一起使用以排除GENERATED ALWAYS 列，或将 DEFAULT 插入 GENERATED ALWAYS列."

我尝试使用 SQL Server 使用以下插入查询直接插入，它的插入很好.

插入 [dbo].[人物]([人物ID]，[名称]，[姓]，[昵称])价值观(2，'约翰'，'数学'，'凉爽的')

请帮助我如何使用 C# Entity Framework 插入记录.

简单总结:当 EF 尝试更新 PERIOD 系统版本控制列中的值时出现问题列属性值由 SQL Server 本身管理.

来自

1. 创建一个自定义命令树拦截器类，该类实现 System.Data.Entity.Infrastructure.Interception.IDbCommandTreeInterceptor 并指定应设置为 ReadOnlyCollection<T>(T 是 DbModificationClause) 在插入或更新修改中不能被 EF 修改:

   内部类 TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor{私有静态 ReadOnlyCollection<DbModificationClause>GenerateSetClauses(IList modifyClauses){var props = new List(modificationClauses);props = props.Where(_ => !_ignoredColumns.Contains((((_ as DbSetClause)?.Property as DbPropertyExpression)?.Property as EdmProperty)?.Name)).ToList();var newSetClauses = new ReadOnlyCollection(props);返回新的SetClauses；}}

2. 仍然在上面的同一个类中，创建被忽略的表名列表并在 INSERT 和 UPDATE 命令中定义操作，该方法应如下所示(此方法归功于 Matt Ruwe):

   //来自/a/40742144私有静态只读列表<字符串>_ignoredColumns = 新列表<字符串>{ "开始时间", "结束时间" };公共无效TreeCreated(DbCommandTreeInterceptionContext拦截上下文){if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace){var insertCommand = interceptionContext.Result as DbInsertCommandTree;如果(插入命令！= null){var newSetClauses = GenerateSetClauses(insertCommand.SetClauses);var newCommand = new DbInsertCommandTree(insertCommand.MetadataWorkspace，insertCommand.DataSpace,插入命令.目标，新的SetClauses，插入命令.返回)；拦截上下文.结果=新命令；}var updateCommand = interceptionContext.Result as DbUpdateCommandTree;如果(更新命令！= null){var newSetClauses = GenerateSetClauses(updateCommand.SetClauses);var newCommand = new DbUpdateCommandTree(updateCommand.MetadataWorkspace，updateCommand.DataSpace,更新命令.目标，updateCommand.谓词，新的SetClauses，更新命令.返回)；拦截上下文.结果=新命令；}}}

3. 在另一个代码部分中使用数据库上下文之前注册上面的拦截器类，方法是使用 DbInterception:

   DbInterception.Add(new TemporalTableCommandTreeInterceptor());

   或使用 DbConfigurationTypeAttribute 将其附加到上下文定义中:

   公共类 CustomDbConfiguration : DbConfiguration{公共自定义数据库配置(){this.AddInterceptor(new TemporalTableCommandTreeInterceptor());}}//来自/a/40302086[DbConfigurationType(typeof(CustomDbConfiguration))]公共部分类 DataContext : System.Data.Entity.DbContext{公共数据上下文(字符串名称或连接字符串):基础(名称或连接字符串){//其他内容或留空}}

相关问题:

实体框架不适用于时态表

从 IDbCommandInterceptor 的实现中获取 DbContext

仅将 IDbInterceptor 挂钩到 EntityFramework DbContext 一次

I'm having issue in Insertion of data in Temporal table using C# Entity Framework

The Table schema is

CREATE TABLE People( 
    PeopleID int PRIMARY KEY NOT NULL, 
    Name varchar(50) Null, 
    LastName varchar(100) NULL, 
    NickName varchar(25), 
    StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL, 
    PERIOD FOR SYSTEM_TIME (StartTime,EndTime) 
) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

I created an EDMX asusal and I tried to Insert a record using following C# Code

using (var db = new DevDBEntities()) {
    People1 peo = new People1() {
        PeopleID = 1,
        Name = "Emma",
        LastName = "Watson",
        NickName = "ICE"
    };

    db.Peoples.Add(peo);
    db.SaveChanges();
}

I got an exception while on db.SaveChanges()

"Cannot insert an explicit value into a GENERATED ALWAYS column in table 'DevDB.dbo.People'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column."

I tried direct insertion using SQL Server using the following Insert Query, its inserting fine.

INSERT INTO [dbo].[People]
           ([PeopleID]
           ,[Name]
           ,[LastName]
           ,[NickName])
     VALUES
           (2
           ,'John'
           ,'Math'
           ,'COOL')

Kindly assist me how to insert an record using C# Entity Framework.

Light summary: The problem occurs when EF trying to update values inside PERIOD system versioning column which the column property values are managed by SQL Server itself.

From MS Docs: Temporal tables, the temporal table works as a pair of current table and history table which explained as this:

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 both StartTime & EndTime column are automatically generated, they must be excluded from any attempt to insert or update values on them. Here are these steps to get rid of the error, assuming you're in EF 6:

1. Open EDMX file in designer mode, set both StartTime & EndTime column properties as Identity in StoreGeneratedPattern option. This prevents EF refreshing values on any UPDATE events.

2. Create a custom command tree interceptor class which implements System.Data.Entity.Infrastructure.Interception.IDbCommandTreeInterceptor and specify set clauses which should be set as ReadOnlyCollection<T> (T is a DbModificationClause) which cannot be modified by EF 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;
       }
   }
   

3. Still in the same class above, create list of ignored table names and define actions in INSERT and UPDATE commands, the method should be looks like this (credits to Matt Ruwe for this method):

   // 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;
           }
       }
   }
   

4. Register the interceptor class above before database context usage in another code part either by using DbInterception:

   DbInterception.Add(new TemporalTableCommandTreeInterceptor());
   

   or attach it in context definition using DbConfigurationTypeAttribute:

   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 issues:

Entity Framework not working with temporal table

Getting DbContext from implementation of IDbCommandInterceptor

Hooking IDbInterceptor to EntityFramework DbContext only once

这篇关于使用 C# Entity Framework 在时态表中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！