How do I get NHibernate to do a join?

Question

I've used Fluent NHibernate to hook up a store and employee class where Stores can have many employees as follows:

public class Store
{
    public virtual IList<Employee> Employees { get; set; }
    //other store properties
}

public class Employee
{
    public virtual Store Store { get; set; }   
    public virtual bool? SomeStatus1 { get; set; }
}

I'm needing to get all stores that have employees that do not have SomeStatus1 set to true.

My feable attempt here has failed:

Session.CreateCriteria(typeof(Store))
    .Add(Restrictions.Not(Restrictions.Eq("Employees.SomeStatus1", true))
    .List<Store>();

Any idea how I go about doing that?

The reason my attempt has failed is because the list Employees doesn't have a property of SomeStatus1...which is fairly obvious.

What I dont know, is how to get NHibernate to only get stores which have employees in the state I'm looking for...

I think what I'm wanting to ask NHibernate is to do a join...but I don't know how to ask it to do that...

Answer 1

you join by creating sub criteria

var criteria = Session.CreateCriteria(typeof(Store));
var join = criteria.CreateCriteria("Employees");
join.Add(Restrictions.Not(Restrictions.Eq("SomeStatus1", true));
return criteria.List<Store>();

Untested (obv) hope it works, but you get the idea. That's how I do it with N:1 but you have 1:N

EDIT: Ok, I did a bit of research after posting. It seems the code I did should work, but will cause loading of the employees collection. The same basic code is found on ayende's blog. There is a sample there which does the same thing without causing the collection to be reloaded. Hope that helps.

Answer 2

I would suggest you use the Linq to NHibernate API instead of the Criteria API. With it, your query would be as follows:

var query = Session.Linq<Store>()
    .Where(store => store.SomeStatus1 != true);

var result = query.ToList();

More help @ http://bit.ly/9CL5c0

Answer 3

Try:

Session.CreateCriteria(typeof(Store))
.CreateAlias("Employees", "e")
.Add(Restrictions.Not(Restrictions.Eq("e.SomeStatus1", true))
.List<Store>();