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

Answer 2

Tha's what I like on NHibernate ... even here is a solution. What we would need is the:

13.3. DML-style operations

Small cite:

As already discussed, automatic and transparent object/relational mapping is concerned with the management of object state. This implies that the object state is available in memory, hence manipulating (using the SQL Data Manipulation Language (DML) statements: INSERT, UPDATE, DELETE) data directly in the database will not affect in-memory state. However, NHibernate provides methods for bulk SQL-style DML statement execution which are performed through the Hibernate Query Language (HQL).

About INSERT (small extract):

The pseudo-syntax for INSERT statements is: INSERT INTO EntityName properties_list select_statement. Some points to note:

• Only the INSERT INTO ... SELECT ... form is supported; not the INSERT INTO ... VALUES ... form.

• The properties_list is analogous to the column speficiation in the SQL INSERT statement. For entities involved in mapped inheritence, only properties directly defined on that given class-level can be used in the properties_list. Superclass properties are not allowed; and subclass properties do not make sense. In other words, INSERT statements are inherently non-polymorphic.
• ...

example from doc:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

var hqlInsert = "insert into DelinquentAccount (id, name) " + 
                " select c.id, c.name from Customer c where ...";
int createdEntities = s.CreateQuery( hqlInsert )
        .ExecuteUpdate();
tx.Commit();
session.Close();

SOLUTION

having this we can create kind of this select:

var hqlSelect = 

// HQL select clause
" SELECT " +
// guid on the DB side
"  UUID(), " +
// this will return matches for today only
"  CAST(SUM(CASE WHEN DAY(created)=DAY(CURRENT_TIMESTAMP) THEN 1 ELSE 0 END) as int),"+
// this will return matches for this month - see WHERE
"  CAST(Count(*) as int)," +
// the time stamp into created
"   CAST(CURRENT_TIMESTAMP as DateTime)" +

// From means - from some entity (the transaction here)
" FROM transaction" +

// Where is here restricting current month
// here we filter just this year and this month records
"   WHERE MONTH(created) = MONTH(CURRENT_TIMESTAMP)  " +
"     AND  YEAR(created) =  YEAR(CURRENT_TIMESTAMP)  ";

And this would be the COMPLETE insert

var hqlInsert = "INSERT INTO AuditLog (id, daily_index, monthly_index, created ) " 
              + hqlSelect; // the above select

And this should do what expected:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

var hqlInsert = ... // see code above to create DML HQL
int createdEntities = session
     .CreateQuery( hqlInsert )
     .ExecuteUpdate();
tx.Commit();
session.Close();