Using NHibernate mapping by code: Cannot insert explicit value for identity column in table 'DietUser' when IDENTITY_INSERT is set to OFF

Question

Took me a while to find an answer for this so thought I'd share the love.

---

When using NHibernate's new mapping by code with SQL Server I'm unable to save an entity. When saving an entity a System.Data.SqlClient.SqlException is thrown with the following message (minus the table name):

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

My table uses an identity ID and the entity & mappings look like this:

public class User
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Username { get; set; }
    public virtual string Password { get; set; }
    public virtual short DailyPoints { get; set; }
}

public class UserMapping : ClassMapping<User>
{
    public UserMapping()
    {
        Id(x => x.Id);
        Property(x => x.Name);
        Property(x => x.Username);
        Property(x => x.Password);
        Property(x => x.DailyPoints);
    }
}

I know how I'd map this using XML mapping but I want to use the built-in mapping by code if at all possible.

Answer 1

After some digging around in the forums I got lucky and found how to map this. The example Fabio provides is great if you're going to use a GUID for an ID or something like that but if you want to use an identity ID (saw a bunch of others as well), you need to specify what generator you're going to use. Here's my updated mapping that works:

public class UserMapping : ClassMapping<User>
{
    public UserMapping()
    {
        Id(x => x.Id, map => map.Generator(Generators.Identity));
        Property(x => x.Name);
        Property(x => x.Username);
        Property(x => x.Password);
        Property(x => x.DailyPoints);
    }
}

Answer 2

Speaking of identity ids, if you set generator=identity, NHibernate would not be able to perform batch insert, because it needs an additional request to obtain this bloody id generated by the database.