使用C#实体框架在时态表中插入记录 [英] Insert Record in Temporal Table using C# Entity Framework

问题描述

我在使用C#实体框架在 Temporal table 中插入数据时遇到问题

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

表架构为

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));

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

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();
}

我在 db.SaveChanges()

I got an exception while on db.SaveChanges()

无法将显式值插入到的GENERATED ALWAYS列中 表"DevDB.dbo.People".将INSERT与列列表一起使用以排除 GENERATED ALWAYS列，或将DEFAULT插入GENERATED ALWAYS 列."

"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."

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

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')

请协助我如何使用C#实体框架插入记录.

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

推荐答案

简要摘要:当EF尝试更新管理列属性值的PERIOD系统版本控制列中的值时，会出现问题通过SQL Server本身.

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.

来自 MS Docs:时态表 ，时态表是一对当前表和历史表，其解释如下:

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

一个表的系统版本实现为一对表，一个 当前表和历史记录表.在每个表格中， 以下两个额外的datetime2列用于定义 每行的有效期:

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:

期间开始列:系统在此列中记录该行的开始时间，通常表示为SysStartTime列.

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

期间结束列::系统在此列中记录该行的结束时间，通常在SysEndTime列中表示.

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

与StartTime和& EndTime列是自动生成的，必须在尝试插入或更新它们的值时将它们排除在外.假设您使用的是EF 6，以下是消除错误的步骤:

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. 在设计器模式下打开EDMX文件，同时设置StartTime& EndTime列属性作为Identity在StoreGeneratedPattern选项中.这样可以防止EF在任何UPDATE事件上刷新值.

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.

1. 创建一个实现System.Data.Entity.Infrastructure.Interception.IDbCommandTreeInterceptor的自定义命令树拦截器类，并指定应设置为 DbModificationClause )，EF不能在插入或更新修改中对其进行修改:

1. 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;
    }
}

仍然与上述相同，创建被忽略表名的列表并在INSERT和UPDATE命令中定义操作，该方法应如下所示(该方法的作者为Matt Ruwe):

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

在其他代码部分使用数据库上下文之前，通过使用DbInterception在上面注册拦截器类:

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

DbInterception.Add(new TemporalTableCommandTreeInterceptor());

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

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

相关问题:

实体框架不适用于时态表

从IDbCommandInterceptor的实现中获取DbContext

仅将IDbInterceptor与EntityFramework DbContext关联一次

这篇关于使用C#实体框架在时态表中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！