Fluent Hibernate Insert Fail, Cannot insert the value NULL into column 'Id',

Question

I am getting an exception on INSERT statement to table using NHibernate

This is how it is called in the Repository (custom) layer

private void AddOrUpdateObject(object objectToUpdate)
{
    UnitOfWork.CurrentSession.SaveOrUpdate(objectToUpdate);
}

This is the SQL Statement generated, i found them in the log

2013-07-08 15:28:43,919 [36] DEBUG NHibernate.SQL - INSERT INTO [Season] (Name, StartDate, EndDate, ModifiedDate) VALUES (@p0, @p1, @p2, @p3); select SCOPE_IDENTITY();@p0 = 'season 4' [Type: String (4000)], @p1 = 6/13/2014 12:00:00 AM [Type: DateTime (0)], @p2 = 12/31/2013 12:00:00 AM [Type: DateTime (0)], @p3 = 7/8/2013 3:28:43 PM [Type: DateTime (0)]

This is the exception I am getting

2013-07-08 15:28:43,929 [36] WARN NHibernate.Util.ADOExceptionReporter - System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'Id', table 'ticketing.social.dbo.Season'; column does not allow nulls. INSERT fails.

Why can't it insert a new row to the table "Season", even if it is using select SCOPE_IDENTITY();

Instead of CurrentSession.SaveorUpdate(), is there another method I could use from ISession?

Please advise, many thanks

Answer 1

Since this is a SQL exception, and your query appears ok, it appears that your ORM is set up correctly, but your database is not. I'd bet your column is not actually an IDENTITY column in the database.

Answer 2

Because you are not setting the ID in your insert list. See:

INSERT INTO [Season] (Name, StartDate, EndDate, ModifiedDate)

No ID there. So the DB tries to insert NULL for ID which is not allowed.

Answer 3

As you're using Fluent NHibernate, I'll assume that you're mapping your classes with Fluent mapping, and not auto mapping.

In your mapping class, you must define how the ID will be generated. I'm assuming that you're using an IDENTITY column because of that `SCOPE_IDENTITY()``call.

In that case, you must map it as:

this.Id(x => x.Id).Column("Id").GeneratedBy.Identity();