Native SQL on NHibernate

I've been working on a project where NHibernate was choosen as the OR/M. The usage policy defined for the adoption of OR/M was:

1. Isolate business rules on a separate business layer (separate from the domain model provided by NHibernate); this decision was partially based on the way tiers are design on our company;
2. Use insert, update and delete directly from the NHibernate domain model;
3. Map directly as relationships whenever possible (ex: Invoice.InvoiceLine will interact transparently with INVOICE_LINES, People.Hobbies will interact transparently with PEOPLE_HOBBIES);
4. Bypass business tier on customized searches - ex: the form that search for Invoices, which will search directly over ICriteria over the OR/M Domain Model;
5. Use as little HQL as possible;
   
6. When none of the previous could be fulfilled, use Native SQL.

Yesterday we finally had to write our first Native SQL code. Ok, we really didn't have to, but we felt this was the time to do it.

Why? We had to reorder a result from a query with a computed criteria, and we didn't want to use views, nor in memory ordering. So here's what we did:

using (TransactionContext transactionContext = new TransactionContext())

{

IList result = transactionContext.Session.CreateSQLQuery(

"Select MyEntity.ID as {MyEntity.Id}," +

“MyEntity.TYPE as {MyEntity.Type}," +

“MyEntity.VALUE as {MyEntity.Value}," +

“MyEntity.PERCENTAGE as {MyEntity.Percentage}," +

“MyEntity.DATA as {MyEntity.Data},

“MyEntity.ID_CONDITION as {MyEntity.Condition}, “

"decode(MyEntity.Tipo, 'E', 0, 'A', 1, 'F', 2, 3) as ordercriterium " +

“FROM T_MY_ENTITY MyEntity inner join T_CONDITION Condition on MyEntity.ID_CONDITION = Condition.ID " +

"order by ordercriterium ",

.AddEntity("MyEntity", typeof(Link.SIGDA.Model.GrandesDireitos.MyEntity))

.List();

// ...

// ...

// ...

}

The integration capability between Native SQL and the Domain Model surpassed my early expectations. Cool!