February 15, 2010 NHibernate, QuickTip
ORDER BY
To add “order by” to your criteria you need this statement.
.AddOrder(Order.Asc(“Priority”))
TOP
To add “top 10” to your criteria you need this statement
.SetMaxResults(10)
Criteria
So code about which I’m talking could look like:
public IList<Customer> FetchTopPriorityCustomers()
{
var result = Session.CreateCriteria(typeof(Customer))
.SetFetchMode(“CustomerStatusType”, FetchMode.Eager)
.Add(Expression.Eq(“CustomerStatusType.CustomerStatusTypeID”, (Int32)CustomerStatusType.Created))
.SetResultTransformer(new DistinctRootEntityResultTransformer())
.AddOrder(Order.Asc(“Priority”))
.SetMaxResults(10)
.List<Customer>();
return result;
}
SetResultTransformer or “Why did I get 5 results instead of 10?”
So you expect to have top 10 priority Customers with status Created.
In scope of my current task it was needed to add priority to this query, so I decided to unit test it of course.
In Debug I found that there are actually 5 results in resulting collection.
That is because generated SQL generates result which contains duplicated CUSTOMER_IDs, that is because I have join-s there. But then why did not I get 10 duplicated Customers? Because query has ResultTransformer which is applied after SQL has been ran. (That is 100% since I took a look at generated SQL via NHibernateProfiler).
So .SetResultTransformer(new DistinctRootEntityResultTransformer()) is removing of all duplicated entries of my root entity (Customer).
Good explanation to this you can find here.
codemore code
~~~~