CODEWITHSUNDEEP
c#sql-serverentity-framework
Bassie
Bassie

Reputation: 10390

SET IDENTITY_INSERT [table] ON not working

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.

To do this I ran this command directly in SSMS on the table:

SET IDENTITY_INSERT [CRMTItem] ON;

Yet, when I insert an item from C# with Id of 1, the Id is still incrementing from around 850.

I deleted the entities from EDMX and updated again from DB but with the same result.

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

But it still doesn't work.

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

And now I get an error

Explicit value must be specified for identity column in table 'CRMTItem' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

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!?!?!

Upvotes: 2

Views: 3035

Answers (1)

Evk
Evk

Reputation: 101483

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.

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

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.

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.

When you just do:

db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[CRMTItem] ON");

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.

Upvotes: 2

Related Questions