NHibernate Insert With SubQuery

Question

I have a table structure of :

------------------------------------------------
|  id  |daily_index|monthly_index|   created   |
------------------------------------------------
| GUID |     1     |      1      |  10-12-2014 |
| GUID |     2     |      2      |  10-12-2014 |
| GUID |     1     |      3      |  11-12-2014 |
| GUID |     1     |      1      |  01-01-2015 |
------------------------------------------------

My goal is to have a transaction object with flexible natural code such as

INV-{daily_index}/{month_in_roman}/{year_in_roman}/{monthly_index}

Or

INV{ddmmyyyyhhiiss}-{monthly_index}-{daily_index}

Or whatever the user wishes it to be.

The class would have a Code property that will weave those private fields, for the sake of UI only.

In pure mysql query, I would do this:

INSERT INTO transaction VALUES (// Some GUID, (SELECT COUNT(*) + 1 FROM transaction WHERE DATE(created) = DATE(NOW)), (SELECT COUNT(*) + 1 FROM transaction WHERE MONTH(created) = MONTH(NOW)), NOW());

My question would be is there a way to reproduce this kind of INSERT mechanism in NHibernate?

I considered another option where I would to a SELECT query with COUNT query, but I don't know if that is possible with NHibernate.

Another option would be making a MySQL trigger, but I would love to know if this is possible to do directly in my project.

Answer 1

NHibernate provides an option for overriding default insert statement: sql-insert:

Using this element in your mapping file, you can change the insert/update statements to your liking like this:

<class name="Student">
  <id name="Id" type="Int32">
    <generator class="assigned" />
  </id>
  <property name="Code" length="2000" />

  <many-to-one name="Class" column="ClassId" not-null="true"/>
  <sql-insert>insert into Student (Code, ClassId, Id) values (UPPER(?), ? , ?)</sql-insert>
</class>

Of course you'll have to customize sql-update also.

Determining the correct position for each column is tricky, though. This is from NH documentation:

You can see the expected order by enabling debug logging for the NHibernate.Persister.Entity level. With this level enabled NHibernate will print out the static SQL that is used to create, update, delete etc. entities. (To see the expected sequence, remember to not include your custom SQL in the mapping files as that will override the NHibernate generated static sql.)

Reference: Custom SQL for create, update and delete