实体框架不使用时态表 [英] Entity Framework not working with temporal table

问题描述

我正在使用数据库优先实体框架 6.在将架构中的一些表更改为临时表后，我在尝试插入新数据时开始收到以下错误:

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:

无法将显式值插入表 '<MyDatabase>.dbo.<MyTableName> 中的 GENERATED ALWAYS 列.将 INSERT 与列列表一起使用以排除 GENERATED ALWAYS 列，或将 DEFAULT 插入 GENERATED ALWAYS 列.

看起来 EF 正在尝试更新由系统管理的 PERIOD 列的值.

It looks like EF is trying to update the values of the PERIOD columns which are managed by the system.

从 EDMX 文件中删除列似乎可以解决问题，但这不是一个可行的解决方案，因为每次从数据库中重新生成模型时都会重新添加列.

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.

推荐答案

这个问题有两种解决方案:

There are two solutions to this problem:

1. 在 EDMX 设计器中列的属性窗口中，将 PERIOD 列(在我的例子中为 ValidFrom 和 ValidTo)上的 StoreGeneratedPattern 更改为 identity.标识优于计算，因为计算将导致 EF 刷新插入和更新上的值，而不是仅使用 identity
的插入
2. 创建一个 IDbCommandTreeInterceptor 实现以删除句点列.这是我的首选解决方案，因为在向模型添加新表时不需要额外的工作.

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 identity. Identity is better than computed 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.

这是我的实现:

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

在使用上下文之前，通过在代码中的任何位置运行以下命令，向 EF 注册此拦截器:

Register this interceptor with EF by running the following anywhere in your code before you use your context:

DbInterception.Add(new TemporalTableCommandTreeInterceptor());

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