Learn NHibernate 6 - Advanced querying of Child Collections

disclaimer - at the moment, this post is in note format from a workshop I did, I need to it clean up, and add grammar
Traversing a relationship

HQL

where there would normally be a join in sql

SQL[sql]
select * from customer c inner join orders o on c.id = o.customerid where c.firstname = 'steve' and o.orderdate > 6/1/2010

[/sql]


but in hql becasue the relationships are already in the mapping file, there is no need to perform the join.

HQL[sql]
select c from customer c, c.orders.elements o where where c.firstname = 'steve' and o.orderdate > 6/1/2010
<div id="_mcePaste">[/sql]


Criteria[csharp]

ICriteria.CreateCriteria(typeof(Customer))
.Add(Expression.Eq("Firstname", "Steve"))
.CreateCriteria("Orders")
.Add(Expression.Gt("OrderDate", new Datetime.Now)))
.List<Customer>();

[/csharp]


Revisiting Data Access

Each mothod gets its own ISesssion instance

Each method is completely atomic.

recalling the pattern[csharp]

using (_SessionFactory.GetSession())
{
//data access happens here
}

[/csharp]


This means no lazy loading as the session has been disposed of already.

Solution? evolve our data provider to introduce a new abstraction

Having the data acess provider create its own ISession is a violation of SRP/SOC principles.

We should be giving the data provider the ISession

Lets add a parameter to the class' constructor.[csharp]

new NHibernateDataProvider(ISession session) {}

[/csharp]


Now something higher up in our object highrachy will be in control of the session. We can now remove all of the using statements in the individual data access methods GetCustomerByiId etc..

We no longer call GetSession anywhere.
Gettting child objects with HQL[csharp]

private void GetCustomersWithOrdersSince(DateTime orderDate) {
return _session.CreateQuery("select c from customer c, c.Orders.elements o where o.OrderDate > : orderDate").SetDateTime("orderDate", orderDate).List<Customer>();
}

[/csharp]

Gettting child objects with Criteria[csharp]

private void GetCustomersWithOrdersSince(DateTime orderDate) {
return _session.CreateCriteria(typeof(Customer))
.CreateCriteria("Orders")
.Add(Expression.Gt("OrderDate", orderDate))
.List<Customer>();
}

[/csharp]

Distinct in Criteria.

If we put the line of code:[csharp]

.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())

[/csharp]


above the List, it will proivide distinct Customer results

Important to understand is that using this distinct transformer in criteria will NOT generate the DISTINCT keyword in the SQL it generates. The HQL does. The transformer will bring back all results and then perform the distinct operation on the client side in NHiberante code. This can be a big performance hit if not understood or used correctly.

If you need to return distinct entities in criteria, add the following line of code to the above query:[csharp]

private void GetCustomersWithOrdersSince(DateTime orderDate) {
var ids = _session.CreateCriteria(typeof(Customer))

.SetProjection(Projections.Distinct(Projections.ProjectionList()

.Add(Projections.Property("CustomerId))))
.CreateCriteria("Orders")
.Add(Expression.Gt("OrderDate", orderDate))
.List<int>();

return _session.CreateCriteria(typeof(Customer))
.Add(Expression.In("CustomerId", ids.ToArray<int>()))
.List<Customer>();
}

[/csharp]


Now the distinct call will be wrapped around hte outside of the original sql generated by the criteria.