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.

Apparatus

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);
    }
}

Apparatus

So are you using GUID as your identity column? – Phthisic

No, just the standard int identity column you'll see in most sql server tables. – Apparatus

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.

Underclothing

Answer 3

Are you trying to assign the value to Id property before persisting the entity? Can you add the peice of code which is trying to persist changes to the database?

While I started learning NHibernate I had built a small demo foloowing the getting started example. Here it is. Hope it helps.

Phthisic

I wasn't, that's what bugged me. I was just setting the Name, Username, Password and DailyPoints. – Apparatus