问题描述
我想在我指定ID的地方插入一些记录，以便将数据迁移到我想保持现有关系完整性的地方。
I want to insert some records where I specify the Id, for the purpose of migrating data where I would like to maintain the integrity of existing relationships.
为此，我直接在表上的SSMS中运行了此命令：
To do this I ran this command directly in SSMS on the table:
SET IDENTITY_INSERT [CRMTItem] ON;
但是，当我从C＃插入ID为1的项时，ID仍从递增850。
Yet, when I insert an item from C# with Id of 1, the Id is still incrementing from around 850.
我从EDMX中删除了实体，并再次从DB中进行了更新，但结果相同。
I deleted the entities from EDMX and updated again from DB but with the same result.
这里是我的插入代码，如您所见，在插入之前，我确保Id确实为1，但这只是被忽略。。
Here is my insert code, where as you can see I am ensuring that the Id is indeed 1 before inserting, yet this just gets ignored..
var crmtItem = new CRMTItem();
crmtItem.Id = adv.PrimaryId;
crmtItem.ProjectTitle = adv.ProjectTitle;
crmtItem.CreatedByUser = (adv.CreatedBy == null) ? (Guid?)null : new Guid(adv.CreatedBy);
crmtItem.Opportunity = (adv.Opportunity == null) ? (Guid?)null : new Guid(adv.Opportunity);
crmtItem.BidNoBid = adv.Bnb;
crmtItem.SPUrl = adv.SPUrl;
crmtItem.BnbId = (adv.BnbId == null) ? (Guid?)null : new Guid(adv.BnbId);
crmtItem.Stage = adv.ProjectStage;
crmtItem.Confidential = adv.Confidential;
crmtItem.OpportunityStatus = adv.OpportunityStatus;
crmtItem.OpportunityNumber = adv.OpportunityNumber;
crmtItem.CRMTNumber = adv.CrmtNumber;
crmtItem.ProjectNumber = adv.ProjectNumber;
crmtItem.Sector = adv.Sector;
crmtItem.Service = adv.Service;
crmtItem.CreatedDate = adv.CreatedDate;
crmtItem.Archive = adv.Archive;
crmtItem.ProjectManager = adv.ProjectManager;
crmtItem.WorkTeam = adv.WorkTeam;
crmtItem.Custodian = adv.Custodian;
db.CRMTItems.Add(crmtItem);
if (adv.PrimaryId == 1 || adv.PrimaryId == 2 || adv.PrimaryId == 3)
{
await db.SaveChangesAsync();
}
在插入项目之前，我也尝试添加此行
I also tried adding this line before inserting the item
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[CRMTItem] ON");
但是它仍然不起作用。
基于我发现的另一个SO问题，我接下来尝试了此方法：
Based on another SO question I found, I tried this next:
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[CRMTItem] ON");
db.CRMTItems.Add(crmtItem);
if (adv.PrimaryId == 1)
{
await db.SaveChangesAsync();
}
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[CRMTItem] OFF");
transaction.Commit();
现在我得到一个错误
我错过了什么吗？为什么控制自己的数据如此困难？如果无法做到这一点，我将不得不在表中创建一个临时列，只是为了存储原始（CDS）表中的ID，这绝对是荒谬的，毕竟这是 MY DATA ，为什么我不能选择列的值！？！？！
Am i missing something? Why must it be so difficult to control my own data? If I can't achieve this, I will be forced to creat a temporary column in my table just to store the id from the original (CDS) table, which is absolutely ridiculous, after all it is MY DATA, why can't I choose the value of the columns!?!?!
推荐答案
从数据库生成模型时-实体框架将使用 StoreGeneratedPattern.Identity 将所有标识列映射到模型属性。就您而言，据我了解，该属性为 crmtItem.Id 。当您插入 crmItem 时-Entity Framework将忽略您为身份属性设置的值（如果您设置了任何值），因为它知道此值是由数据库提供的，因此它知道是否试图在插入语句中提供这样的值-数据库将返回错误。
When you generate your model from database - Entity Framework will map all identity columns to model properties with StoreGeneratedPattern.Identity. In your case, such property is crmtItem.Id as I understand. When you insert crmItem - Entity Framework will ignore value you set for identity property (if you set any), because it knows this value is provided by database, so it knows if it tries to provide such value in insert statement - database will return an error.
实体框架不了解 IDENTITY_INSERT ，因此它将始终根据目标模型属性的 StoreGeneratedPattern 元数据运行。如果它是身份或已计算-无论您做什么，它都不会在插入中为其提供价值。如果将它设置为 None -它将提供一个值（无论如何）。
Entity Framework has no knowledge of IDENTITY_INSERT, so it will always behave according to StoreGeneratedPattern metadata of target model property. If it's Identity or Computed - it will not provide value for it in insert, whatever you do. If it's set to None - then it will provide a value (no matter what).
所以对于您的情况您需要在EDMX设计器中将此属性设置为 None 作为目标属性（ CRMTItem.Id ）。当然，这样做之后-您将必须在插入时始终提供此值。
So for your case you need to set this attribute to None in EDMX designer for target property (CRMTItem.Id). Of course after doing that - you will have to always provide this value while inserting.
问题的另一部分是 IDENTITY_INSERT 不被尊重，您已经解决了，但仍然值得解释。此设置是基于会话的，因此，当您仅在SSMS中执行它，然后尝试从您的应用程序中插入时，它不会起作用：SSMS和您的应用程序处于不同的会话中。
Another part of the problem, with IDENTITY_INSERT being not respected, you already solved but still worth some explanation. This setting is session-scoped, so when you just execute it in SSMS and then try to insert from your application - it has no effect: SSMS and your application are in different sessions.
刚做的时候：
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[CRMTItem] ON");
此操作仍在单独的会话中执行，而不是在您的 SaveChanges中执行将执行。因此，要在同一会话中同时执行 IDENTITY_INSERT 和 SaveChanges -您需要将它们包装在事务中，正如您已经想到的那样
This still executes in separate session, not in the same your SaveChanges will execute. So to execute both IDENTITY_INSERT and SaveChanges in the same session - you need to wrap them in transaction, as you already figured out.
这篇关于SET IDENTITY_INSERT [表格]开不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！