nhibernate alternate id's using generated prop

问题:

** This question has been edited to make it simpler and more focused **

Employee has an EmployeeNumberValue property which I would like to have auto-incremented by the db. To the business domain, this is a unique id assigned to employees and used to identify them on employee cards, etc. To the database however, it is an alternate id and not the primary key.

NHib has a documented ability called Generated Properties. Per the docs, "generated properties are properties which have their values generated by the database. Typically, NHibernate applications needed to Refresh objects which contain any properties for which the database was generating values. Marking properties as generated, however, lets the application delegate this responsibility to NHibernate. Essentially, whenever NHibernate issues an SQL INSERT or UPDATE for an entity which has defined generated properties, it immediately issues a select afterwards to retrieve the generated values."

The problem I am having is that while NHib is making the additional SELECT to update the EmployeeNumberValue, it is not assigning the retrieved value to the property.

Can anyone see why this is happening what the fix is?

Cheers,
Berryl

FAILING TEST AND OUTPUT (tested w/ SQLite in memory db):

    [Test]
    public void Employee_OnInsert_EmployeeNumberValueIsIncremented() {

        var emp1 = new Employee
        {
            FullName = _fullName,
            Department = _department,
        };
        var emp2 = new Employee
        {
            FullName = _fullName,
            Department = _department,
        };

        var session = _SessionFactory.GetCurrentSession(); 

        using (var tx = session.BeginTransaction())
        {
            session.Save(_department);
            session.Save(emp1);
            session.Save(emp2);
            tx.Commit();
        }
        Assert.That(emp1.EmployeeNumberValue, Is.EqualTo(1));
        Assert.That(emp2.EmployeeNumberValue, Is.EqualTo(2));
    }

NHibernate: INSERT INTO Employees (FirstName, LastName, DepartmentId, EmployeeId) 
        VALUES (@p0, @p1, @p2, @p3);@p0 = 'Berryl' [Type: String (0)], @p1 = 'Hesh' [Type: String (0)], @p2 = 32768 [Type: Int32 (0)], @p3 = 65536 [Type: Int32 (0)]
NHibernate: SELECT employee_.EmployeeNumberValue as Employee2_1_ FROM Employees employee_ WHERE employee_.EmployeeId=@p0;@p0 = 65536 [Type: Int32 (0)]
NHibernate: INSERT INTO Employees (FirstName, LastName, DepartmentId, EmployeeId) 
        VALUES (@p0, @p1, @p2, @p3);@p0 = 'Berryl' [Type: String (0)], @p1 = 'Hesh' [Type: String (0)], @p2 = 32768 [Type: Int32 (0)], @p3 = 65537 [Type: Int32 (0)]
NHibernate: SELECT employee_.EmployeeNumberValue as Employee2_1_ FROM Employees employee_ WHERE employee_.EmployeeId=@p0;@p0 = 65537 [Type: Int32 (0)]
Test failed: 
   Expected: 1
   But was:  0

OBJECT MODEL

public class Employee : Entity, IResource
{
    public virtual long EmployeeNumberValue { get; set; }

    ...
}

MAPPING:

  <class name="Employee" table="Employees">

<id name="Id" unsaved-value="0">
  <column name="EmployeeId" />
  <generator class="hilo" />
</id>

<property name="EmployeeNumberValue" generated="insert" insert="false" update="false" >
  <column name="EmployeeNumberValue" sql-type="int IDENTITY(1,1)" index="IDX_EmployeeNumber"  />      
</property>

...

create table Employees (
    EmployeeId INTEGER not null,
   EmployeeNumberValue int IDENTITY(1,1),
   FirstName TEXT not null,
   LastName TEXT not null,
   DepartmentId INTEGER,
   primary key (EmployeeId)
)

I suspect the way I am marking the column as IDENTITY is also suspect. I tried using database-object as below, but got a usage error in doing so

  <database-object>
    <create>
      ALTER TABLE Employee DROP COLUMN EmployeeNumberValue
      ALTER TABLE Employee ADD EmployeeNumberValue INT IDENTITY
    </create>
    <drop>
      ALTER TABLE Employee DROP COLUMN EmployeeNumberValue
    </drop>
  </database-object>

SQLiteException : SQLite error  "DROP": syntax error

回答1:

From a design perpective I wouldn't rely on NHibernate in this case. What I mean is, that in your domain model, you want an employee to get a new employee card number.

In this case I would only allow an employee to be instantiated if there is a card number.

public class EmployeeCardNumber
{
    private string id = String.Empty;
    internal EmployeeCardNumber(string id)
    {
        this.id = id;
    }
}


public class Employee
{
    private EmployeeCardNumber employeeCardNumber;

    public EmployeeCardNumber CardNumber { ... }

    public Employee(EmployeeCardNumber employeeCardNumber)
    {
        this.employeeCardNumber = employeeCardNumber;
    }
}

So now you have to think about how to generate a unique EmployeeCardNumber.

public class EmployeeCardNumberFactory
{
    public EmployeeCardNumber CreateNew()
    {
        // in this example the card number will be a guid.
        // but you could also implement a "EmployeeCardNumberGenerator" class which will do crazy database stuff
        return new EmployeeCardNumber(Guid.NewGuid().ToString());
    }
}

Then you would later do:

EmployeeCardNumber cardNumber = employeeCardNumberFactory.CreateNew();
Employee employee = new Employee(cardNumber, name, etc...);

Addition: To generate a "EmployeeCardNumber" via database, you could just map "EmployeeCardNumber" to an extra table "EmployeeCardNumber" that will serve as your identity generator like:

<class name="EmployeeCardNumber" table="EmployeeCardNumber">
    <id name="id" access="field" unsaved-value="0">
      <column name="EmployeeCardNumberId" />
      <generator class="identity" />
    </id>
</class>

Then in the factory you could do:

public class EmployeeCardNumberFactory
{
    private IEmployeeCardNumberRepository repository = new EmployeeCardNumberRepository(); // inject...
    public EmployeeCardNumber CreateNew()
    {     
        EmployeeCardNumber cardNumber = new EmployeeCardNumber();
        repository.Save(cardNumber); // gets you a fresh id
        return cardNumber;
    }
}

回答2:

While that's doable, it's better to do it in the DB (using identity or a trigger) and map the property as generated on insert.

Check 5.5. Generated Properties

回答3:

I had same scenario and it works very well in production.

Here is mapping (generated by Fluent NHibernate):

<property generated="insert" name="Number" update="false" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
  <column name="Number" not-null="true" />
</property>

In database this column looks like this:

ALTER TABLE [DeviceLink] ADD [Number] INT not null IDENTITY (1, 1)