在Entity Framework Core中为多个表设置SET IDENTITY_INSERT [英] Setting SET IDENTITY_INSERT for multiple tables in Entity Framework Core

问题描述

我想将表 IDENTITY_INSERT 设置为 ON 。我一次只能坐一张桌子。但是当我执行代码优先方法时，如何实现一个以上的目标。

I want to set tables IDENTITY_INSERT to ON. I can for one table at a time. But how can I achieve for more than one as I am doing code-first approach.

我遇到了以下错误：

System.Data.SqlClient.SqlException：表'Some Table'的IDENTITY_INSERT已经打开。无法对表'ref.EmploymentType'进行SET操作

System.Data.SqlClient.SqlException : IDENTITY_INSERT is already ON for table 'Some Table'. Cannot perform SET operation for table 'ref.EmploymentType'

Test.cs

using (var transaction = _referenceDataDbContext.Database.BeginTransaction())
{
    _referenceDataDbContext.EmploymentType.AddRangeAsync(
                new EmploymentTypeEntity
                {
                    EmploymentTypeID = 1,
                    EmploymentType = "EmploymentType1 ",
                    CategoryTypeID = 27,
                    SiteAddress = null,
                    CreatedBy = "UnitTest",
                    CreatedOn = DateTime.Now,
                    ModifiedBy = "UnitTest",
                    ModifiedOn = DateTime.Now,
                    RowVersion = new RowVersion(1),
                    EmploymentTypeGroups = new[]
                    {
                    new EmploymentTypeGroupEntity
                    {
                        EmploymentTypeGroupID = 11, GroupName = "GroupName", IsActive = true
                    }
                    }
                }
                }
            );

    _referenceDataDbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [ref].[EmploymentTypeGroup] ON");
    _referenceDataDbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [ref].[EmploymentType] ON");

    _referenceDataDbContext.SaveChanges();
}

推荐答案

删除这样的行：

 EmploymentTypeGroups = new[]
 {
     new EmploymentTypeGroupEntity
     {
         EmploymentTypeGroupID = 71, GroupName="Some Data", IsActive = true
     }
 }

并将 _referenceDataDbContext.Database.ExecuteSqlCommand（ SET IDENTITY_INSERT [ref]。[EmploymentType] ON）; 移到 _referenceDataDbContext.EmploymentType上方.AddRangeAsync（行。

然后关闭IDENTITY_INSERT。

then turn IDENTITY_INSERT OFF.

然后重复整个过程可以插入组记录。

Then repeat the whole thing to insert your group records.

这样，您一次只需要为一张表IDENTITY_INSERT ON。

This way you only need IDENTITY_INSERT ON for one table at a time.

这篇关于在Entity Framework Core中为多个表设置SET IDENTITY_INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！