Determine field by formula on Insert in NHibernate

Question

Hello, I am using NHibernate 2.1. I am inserting records into a table successfully, but I have a SortOrder column that needs to be calculated on insert. The formula will roughly be:

SortOrder = (SELECT (MAX(SortOrder) + 1) FROM MyTable WHERE CategoryID = @CategoryID)

How can I accomplish this in NHibernate on inserts? Normally I use Stored Procedures and I would just include this there.

Thanks for any help!

Answer 1

I am not aware of a way of doing what you ask through the mapping files. I do not think there is one.

How I would approach the problem is to plug in a PreInsertListener and do the select statement you provide in your question there to retrieve the value for the SortOrder answer just before the entity is saved.

Here is how it would roughly look:

public class NHibernateEventListeners : IPreInsertEventListener
{
    public bool OnPreInsert(PreInsertEvent auditEvent)
    {
     var audit = auditEvent.Entity as YourEntityTypeHere;

     if (audit == null)
      return false;

     int sortOrderValue = 
           (int)auditEvent.Session.CreateCriteria<YourEntityTypeHere>()
       .SetProjection(Projections.Max("SortOrder"))
       .Add(Restrictions.Eq("CategoryID", CatID)).UniqueResult();

     SetValue(auditEvent.Persister, auditEvent.State, "SortOrder", sortOrderValue + 1);

     audit.DateCreated = sortOrderValue + 1;

     return false;
    }
}

Answer 2

You can still use stored procs with nhibernate. You need to implement <sql-insert>, <sql-update> and <sql-delete> in the xml mapping file to do any specialized CRUD. I think that will be your best bet given your scenario.

Answer 3

In your class mapping file, you can specify a SQL formula for a column / property. Using XML mapping files:

<property name="SortOrder"  formula=" (SELECT (MAX(SortOrder) + 1) FROM MyTable WHERE CategoryID = @CategoryID)"/>

Or if you are using fluent mapping:

Map(x => x.SortOrder).FormulaIs(" (SELECT (MAX(SortOrder) + 1) FROM MyTable WHERE CategoryID = @CategoryID)");

See if that works.

Answer 4

You could write a trigger in your database but be aware of the problems with this approach as NHibernate won't be aware of the changes in this calculated field. You have to use Flush() method after saving and Refresh(your_data_object) method after it.

There is a good detailed explanation about dealing with triggers in "NHibernate in Action" book: "9.2.4 Working with triggers".