Entity Framework Double PK Overwrite Gotcha

Reading Time: 2 minutes

I was writing some unit tests: largely out of completeness I wanted to test that you couldn’t insert two records with the same primary key. The code is simple enough.

    var car = db.Cars.OrderBy(g => Guid.NewGuid()).First();
    var pool1 = db.Pools.OrderBy(g => Guid.NewGuid()).First();
    var driverName1 = Guid.NewGuid().ToString().Trim('{', '}');
    var created1 = PoolAllocatedCar.Create(car, pool1, driverName1);
    db.PoolAllocatedCars.Add(created1);

    var pool2 = db.Pools.OrderBy(g => Guid.NewGuid()).First();
    var driverName2 = Guid.NewGuid().ToString().Trim('{', '}');
    var created2 = PoolAllocatedCar.Create(car, pool2, driverName2);
    db.PoolAllocatedCars.Add(created2);

    Assert.Catch<Exception>(()=> db.SaveChanges());

Car Id is the sole primary key of the PoolAllocatedCars table.

Being bit lazy I guessed it was quicker for me to run the code and find out what exception SaveChanges() it threw rather than trawl through the docs and work out what it should be.

The problem: it didn’t throw an exception. So I added some debug to find out what happened, the result is disappointing to say the least.

---===*** FAILED TO NOTICE PK CLASH ***===---

In Memory:
    Car Id [24] Pool Id [49] Driver [bda1d05c-8dae-4648-ab42-736eb8c44b71]
    Car Id [24] Pool Id [08] Driver [9dae73a0-5b8d-45bc-9d0a-f8b73141aa2c]

In Database:
    Car Id [24] Pool Id [08] Driver [9dae73a0-5b8d-45bc-9d0a-f8b73141aa2c]

It would appear that Entity Framework simply overwrote the first record with the second without giving any indication that there was ever a primary key clash.

Now I’m sure that somewhere in the documentation there’s a warning or a note about this but I haven’t found it yet…

Update: It Gets Worse

I was taken aback by the above, that it could be deemed acceptable to treat the explicit addition of a second object with the same primary key as an implicit update with no warning to the user.

I guess then I shouldn’t have been surprised that it even does this after an explicit call to SaveChanges()

The following test gives exactly the same result as the first. This is a massive gotcha.

    var car = db.Cars.OrderBy(g => Guid.NewGuid()).First();
    var pool1 = db.Pools.OrderBy(g => Guid.NewGuid()).First();
    var driverName1 = Guid.NewGuid().ToString().Trim('{', '}');
    var created1 = PoolAllocatedCar.Create(car, pool1, driverName1);
    db.PoolAllocatedCars.Add(created1);

    db.SaveChanges();

    var pool2 = db.Pools.OrderBy(g => Guid.NewGuid()).First();
    var driverName2 = Guid.NewGuid().ToString().Trim('{', '}');
    var created2 = PoolAllocatedCar.Create(car, pool2, driverName2);
    db.PoolAllocatedCars.Add(created2);

    Assert.Catch<Exception>(()=> db.SaveChanges());

The good news is that if you use a different DbContext it throws an exception :- in fact on the Add, not the SaveChanges.

I’m having trouble getting my head around this: I can’t see the logic. If you thought there was a chance that you’d want to update an object after you’d added it to the table then you should keep your own reference to it. If that’s a problem because of scope then your design is probably wrong.