CODEWITHSUNDEEP
c#sql-serverentity-frameworkazure
Ian
Ian

Reputation: 3149

Entity Framework : Insert causes violation of PRIMARY KEY constraint using Azure SQL, not local

I'm in the process of migrating a local SQL Server 2014 database for use in Azure. I have an Asp.net MVC app running in IIS 8, connected to an Azure SQL Server database using Entity Framework in the usual way.

  1. I have a dbContext
  2. Using(... dbContext.Database.BeginTransaction())
  3. other entities added and SaveChanges() runs successfully
  4. For one entity in particular, I do db.entity.add(newEntity), then SaveChanges() and I get:

    Exception: Violation of PRIMARY KEY constraint 'PK_entity'. Cannot insert duplicate key in object 'dbo.entity'. The duplicate key value is (2).

The primary key is an auto increment number and I'm the only user running this. There are 156 records in this table already.

What's bizarre is that this works fine when I run this on my local machine using a local SQL Server instance, but when run from IIS and Azure db, this exception occurs.

The entity has no other relations/constraints at all.

I do feel there may be a clue in that if I keep trying to save this record (ISS and Azure), the failed duplicate key value increments by 1 each time.

Upvotes: 1

Views: 973

Answers (1)

Rui Estreito
Rui Estreito

Reputation: 272

That´s very strange, maybe the index of identity coll in Azure Bd is set to 1. You can force your identity coll to a index greater than your last Id in table:

In sql server you can check next Id:

SELECT IDENT_CURRENT ('tablename') AS Current_Identity;

You can force it to other number:

DBCC CHECKIDENT ('tablename', RESEED, 157);

Upvotes: 3

Related Questions