Explicit value for identity column error on insert with nHibernate relationship

Question

I have 2 NHibernate entities (tables) that are in a one to many relationship.

I am getting this error:

Cannot insert explicit value for identity column in table 'Bookings_Locations' when IDENTITY_INSERT is set to OFF.

when trying to save (insert) new records.

The entities

public class Booking
{
    public virtual int Id { get; set; }

    public virtual void AddBookingLocation(BookingLocation bookingLocationToAdd)
    {
        bookingLocationToAdd.Booking = this;
        this.BookingLocations.Add(bookingLocationToAdd);
    }

    public virtual void RemoveBookingLocation(BookingLocation bookingLocationToRemove)
    {
        this.BookingLocations.Remove(bookingLocationToRemove);
    }

    public virtual IList<BookingLocation> BookingLocations { get; set; }

    public Booking()
    {
        BookingLocations = new List<BookingLocation>();
    }
}

public BookingMap()
{
    Table("Bookings");

    Id(x => x.Id).Column("ID");
    HasMany(x => x.BookingLocations)
       .KeyColumn("ID")
      .Not.LazyLoad().Cascade.All(); 
}

public class BookingLocation
{
    public virtual int Id { get; set; }
    public virtual Booking Booking { get; set; }
}

public BookingLocationMap()
{
    Table("Bookings_Locations");    

    Id(x => x.Id).Column("ID");

    References(x => x.Booking)             
      .Column("ID")             
      .Not.LazyLoad().Nullable()
      .Cascade.All();
}

The value of BookingLocation's primary key (ID) is 0 as soon as it's initialized and I'm not assigning anything to it. Which leads me to believe that the problem is somehow in relationship mapping.

I do this before calling the method in the screenshot:

Booking.AddBookingLocation(BookingLocation);
this.bookingRepo.insertBooking(Booking, BookingLocation); 

I haven't done a relationship like this before so could be way off...

Any help appreciated :)

Edit:

I now have an error:

"Object reference not set to an instance of an object."

...when SessionFactory.OpenSession runs.

It's something to do with:

public class BookingLocation
{
        public BookingLocation(Booking inBooking)
        {
            this.Booking = inBooking;
            inBooking.AddBookingLocation(this);
        } 
}

...because when I remove the constructor stuff it runs (thought without adding the BookingLocation)

I guess at the time it opens the database there's no instance being passed into the constructor as per your example. Do you know what I mean?

Answer 1

---

I think this is not an NHibernate-specific error, but SqlServer -- specifically the table schema.

As I understand it, you're not setting the PK values on the client, and you're expecting the ID to be generated at the database, correct?

The error you're getting is from SqlServer indicating that the table is configured for this, but you're sending an ID to the database.

Enabling this seems pretty trivial.

http://sqlserverplanet.com/sql-server/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity_insert-is-set-to-off

But maybe this isn't what you want to do.

This example causes a problem when inserting into a table that has an Identity column, when the insert is specifying the ID that should be inserted, rather than let the table generate it .. ie, table1 starts incrementing values at 1, while table2 starts at 4. The example inserts a record successfully into table1 (this row would have ID:1), then tries to insert that same row, ID and all, into table2. Normally, when inserting into an table with an Identity column, you don't specify the ID, as the table will generate it for you.

Perhaps try setting the DefaultValue on in the mapping to 0, in addition to the GeneratedBy suggested by Anton. This might prevent NHibernate from sending the ID column value of 0. This is assuming you aren't attempting to generate IDs in the app that should be directly inserted into the DB.

---

answer to comment:

If the Booking class needs a collection of BookingLocations, the BookingLocation table needs a FK like BookingID. (aka, SELECT * from BookingLocation WHERE BookingID = 123, gets you the list of BookingLocations for Booking 123)

To Map this relationship, your BookingMap should look like this (just showing the collection mapping)

HasMany(x => x.BookingLocations)
   .KeyColumn("BookingID") // aka, column in BookingLocation that links a row to this Booking
   .Not.LazyLoad()
   //.Cascade.All() // would advise against this until you can get it working explicitly
   .Inverse() // use this instead
   ;

try changing your References mapping like so:

References(x => x.Booking)             
   .Column("BookingID") // the column in the locations table that points to the booking.
   .Not.LazyLoad()
   ;

Marking this collection as Cascade.All means that you should not have to manually persist (session.Save) the BookingLocation.

And it also means that if you ever save the Booking, you need to make sure you have loaded the BookingLocations, because I think without them being loaded, it will make your BookingLocation table look like the collection inside Booking .. in this case, since it's empty, it will delete the BookingLocations. Similarly, you probably want to remove the Cascade.All from the Booking References mapping in BookingLocation. i.e. If you delete a single BookingLocation, you don't want to delete the Booking associated with it - there may be other BookingLocations.

Generally, I don't use Cascade in my mappings, and instead set my collections as Inverse(). However, by using Inverse collection mapping, BookingLocations won't be saved automatically when you save a Booking. You will have to go back to saving them manually:

using (var session = CreateNewSession())
{
    using (var trans = session.BeginTransaction())
    {
        session.Save(myNewBooking)
        myNewBooking.BookingLocations.ForEach(x => session.Save(x));
        trans.Commit();
    }
}

In summary:

  Bookings (table)
  ID

  Booking_Locations (table)
  ID
  BookingID


  Booking (class)
  ID
  IList<BookingLocation>

  BookingLocation (class)
  ID
  Booking (instance)

BookingMap

base.HasMany<BookingLocation>(x => x.BookingLocations)
.KeyColumn("BookingID")
.Inverse()
.Not.LazyLoad()
;

BookingLocationMap

base.References<Booking>(x => x.Booking)
.Column("BookingID")
.Not.Nullable()
;

saving

var newBooking = new Booking(); // new booking
var newBLocation = new BookingLocation(); // new booking location
newBLocation.Booking = newBooking; // set booking instance on booking location
newBooking.BookingLocations.Add(newBLocation); // add new booking location to booking

session.Save(newBooking); // get the newBooking's ID from the DB insert
session.Save(newBLocation); // save the new location. NHibernate will set the FK col BookingID to the ID of the newly-persisted newBooking, and also get the new ID for newBLocation