CODEWITHSUNDEEP
c#sqlentity-framework
DerSeegler
DerSeegler

Reputation: 165

How do I handle UpdateException - 'Violation of PRIMARY KEY constraint' on Entity Framework?

I have an appication that allows multiple users and a database table which has 2 IDs as a compound key. These IDs are also foreign keys from another table. So when 2 users try to add an entry to this tabel with the same IDs one of them gets an UpdateException because of Primary Key constaint violation. I already found out that it should be handled like this:

try
{
    result = base.SaveChanges(options);
}
catch (UpdateException ex)
{
    SqlException innerException = ex.InnerException as SqlException;
    if (innerException != null && innerException.Number == 2627 || innerException.Number == 2601)
    {
        // handle here
    }
    else
    {
        throw;
    }
}

But what do I actually do on the "//Handle here" part. I tried refreshing the object but it is in the "Added" state and therefor can not be refreshed. What I whant it to do is: Acknowledge that there is already an object with these IDs, drop its object that it wanted to insert and load the existing object from the database. How can I do that?

Upvotes: 7

Views: 6966

Answers (4)

DerSeegler
DerSeegler

Reputation: 165

Since I got an upvote I looked back how I solved this. So here is what I did:

// Exception number 2627 = Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.
// Exception number 2601 = Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.
// See http://msdn.microsoft.com/en-us/library/cc645603.aspx for more information and possible exception numbers
if (innerException != null && (innerException.Number == 2627 || innerException.Number == 2601))
{
    // Resolve the primary key conflict by refreshing and letting the store win
    // In order to be able to refresh the entity its state has to be changed from Added to Unchanged
    ObjectStateEntry ose = ex.StateEntries.Single();
    this.ObjectStateManager.ChangeObjectState(ose.Entity, EntityState.Unchanged);
    base.Refresh(RefreshMode.StoreWins, ose.Entity);

    // Refresh addedChanges now to remove the refreshed entry from it
    addedChanges = this.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added).Where(s => !s.IsRelationship);
}
else
{
    throw;
}

Edit:

Note that UpdateException has been renamed to DbUpdateException starting with EF 4.1.

Upvotes: 3

Musical Coder
Musical Coder

Reputation: 468

I had to use the following for it to work for me - I was using an async function, but I've tested and it works without the async...

try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
    SqlException innerException = ex.InnerException.InnerException as SqlException;
    if (innerException != null && (innerException.Number == 2627 || innerException.Number == 2601))
    {
        //your handling stuff
    }
    else
    {
        throw;
    }
}

I found that if I didn't use DbUpdateException, it just passed right over the catch, and my ex.InnerException and an additional InnerException object that I had dive into in order to make it to the error number...

Upvotes: 1

Anders Martini
Anders Martini

Reputation: 948

Unless I've completely misunderstood what you are trying to do, I think you may be tackling this problem from the wrong direction.

instead of just trying to write your changes to the database and then handling the conflicting Primary Key Constraint when you get an error, Check for an object with the relevant Id before writing, handle if it exists, if not, continue normally :

if(base.object.Any(o=> o.Id == command.Id){
///Object exists, your "// handle here" goes here...
}else{
///No such object, save your changes normally
}

assuming base is your database-variable... Hope this might help you!

Upvotes: 0

David
David

Reputation: 4953

So when 2 users try to add an entry to this tabel with the same IDs one of them gets an UpdateException because of Primary Key constaint violation.

Correct--if you have a primary key (whether that's with 1 column or many), it must be unique.

What I whant it to do is: Acknowledge that there is already an object with these IDs, drop its object that it wanted to insert and load the existing object from the database.

This is a bit hard to answer since we don't know things like what this method is supposed to return, etc.

  • We would have to know that before we could advise on how you should "Acknowledge".
  • Dropping the object is easy--you just don't do anything else with it.
  • Finally, you want to load the existing object. Is this necessary? What are you going to do with it once it is loaded? Let's say you wanted update the existing object with the values passed in (I wouldn't, but let's say that's what you needed to do). All you would need to do is read it from the DB into a new object, update the fields you want to change and then save it.

Here is one possible example:

var exisitingEntity = context.TheEntity;
existingEntity.Property1 = options.Property1;
existingEntity.Property2 = options.Property2;
...
context.SaveChanges(existingEntity);
return "Object already existed, but was updated with the values passed in."

Upvotes: 0

Related Questions