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

问题描述

我正在使用数据库第一个实体框架6.将我的模式中的一些表更改为时间表后，尝试插入新数据时，我开始收到以下错误：

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

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

从EDMX文件中删除列似乎可以解决问题，但这不是一个可行的解决方案，因为每次从数据库。

解决方案

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

1. 在EDMX设计器的列的属性窗口中，更改 PERIOD StoreGeneratedPattern >列（在我的例子中为ValidFrom和ValidTo）为 identity 或计算的。身份可能更好，因为计算将导致EF刷新插入和更新的值，而不是只插入标识


2. 创建一个 IDbCommandTreeInterceptor 实现来删除句点列。这是我的首选解决方案，因为在模型中添加新表时不需要额外的工作。

这是我的实现：

 内部类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结果为DbInsertCommandTree; 
 if（insertCommand！= null）
 {
 var newSetClauses = GenerateSetClauses（insertCommand.SetClauses）; 
 
 var newCommand = new DbInsertCommandTree（
 insertCommand.MetadataWorkspace，
 insertCommand.DataSpace，
 insertCommand.Target，
 newSetClauses，
 insertCommand。返回）; 
 
 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; 
} 
} 
} 
 
私有静态ReadOnlyCollection< DbModificationClause> GenerateSetClauses（IList< DbModificationClause> modifyClauses）
 {
 var props = new List< DbModificationClause>（modificationClauses）; 
 props = props.Where（_ =>！_namesToIgnore.Contains（（（作为DbSetClause）？属性为DbPropertyExpression）？属性为EdmProperty）？。Name））ToList（）; 
 
 var newSetClauses = new ReadOnlyCollection< DbModificationClause>（props）; 
 return newSetClauses; 
} 
} 
  

通过在EF中注册此拦截器，你的代码在你使用上下文之前：

  DbInterception.Add（new TemporalTableCommandTreeInterceptor（））; 
  

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:

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 which are managed by the system.

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. 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 either identity or computed. Identity is probably better 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.

Here's my implementation:

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 running the following anywhere in your code before you use your context:

DbInterception.Add(new TemporalTableCommandTreeInterceptor());

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