CODEWITHSUNDEEP
c#sql-servernhibernatefluent-nhibernate
rabejens
rabejens

Reputation: 8122

Fluent NHibernate: "Cannot insert duplicate key in object" even though Id is GeneratedBy.Native() or Identity()

I have a fairly simple table with the appropriate Fluent NHibernate mapping like this:

public class Blurb {

    public virtual byte SimpleId { get; set; }
    public virtual string Foo { get; set; }
    public virtual string Bar { get; set; }
}

public class BlurbMap: ClassMap<Blurb> {

    public BlurbMap() {
        Id(x => x.SimpleId).Column("PK_SimpleID").GeneratedBy.Identity();
        Map(x => x.Foo);
        Map(x => x.Bar);
        Table("dbo.SimpleTable");
    }
}

Please don't judge me for using byte as the ID, the database came pre-made by a third party and I can't change it.

I now want to insert something, so I do:

var blurb = new Blurb {
    Foo = "foo",
    Bar = "bar"
};

using(var txn = session.BeginTransaction()) {
    session.SaveOrUpdate(blurb); // <- boom!
    txn.Commit();
}

At where I wrote "boom", I get the exception:

could not insert: [Foo.Bar.Blurb][SQL: INSERT INTO dbo.SimpleTable (Foo, Bar) VALUES (?, ?); select SCOPE_IDENTITY()] ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_SimpleID'. Cannot insert duplicate key in object 'dbo.SimpleTable'. The duplicate key value is (1).

I tried the insert again with SSMS, and I get the same error, but with subsequent executes of the query, the error message changes like:

The duplicate key value is (1)
The duplicate key value is (2)
The duplicate key value is (3)
...

up until hitting an unused key when the insert succeeds.

I already tried GeneratedBy.Native() to no avail. Currently, I can only think of a workaround like

var lastBlurb = session.Query<Blurb>().Select(x => x.SimpleId).Max();
newBlurb.SimpleId = lastBlurb + 1;
session.SaveOrUpdate(newBlurb);

this would be feasible because the "real" table only holds like 10 to 20 elements and there is only ever one program doing write access to the table (yes, we really know that and can be sure about that!) but might there be another way around this? Or did the (external) database designer do something wrong?

Upvotes: 0

Views: 841

Answers (1)

rabejens
rabejens

Reputation: 8122

I post this as an answer because it is lengthy.

Turned out that the problem was a whole different story. I ran into this on my test database. I run a copy of the database on my development system for integration tests. After each test run, the database is cleaned out and reset to the state where it was in when I received it from the third party.

And in that resetting process, I have this little guy:

EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"

And this sets the seed of each identity column to 0, hence trying to insert a new record causes the error. d'oh!

I now changed this to use the correct values for each table, issuing separate of the DBCC CHECKIDENT commands and the problem went away.

Head -> Desk -> bang...

Upvotes: 2

Related Questions