How to use NHibernate to retrieve elements with a criteria on a List

Question

Hi Folks

I'm using NHibernate and I have the two following classes which map my DataBase schema:

public class A
{
    public virtual int Id { get; set;}
    public virtual List<B> MyList { get; set; }
}

public class B
{
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual A FKtoA { get; set; }
}

I would like to get all the entries of table A that have all the elements of their MyList property with a Date less than a given value.

How can I do that with an elegant NHibernate syntax?

Answer 1

Use this

ICriteria criteria =  session.CreateCriteria<ClassOfTableOne>();
criteria.CreateAlias("FieldNameOfTypeTable2","aliasName");
criteria.SetFetchMode("aliasName", FetchMode.Join);
criteria.Add(Restrictions.Lt("aliasName.Date", yourdate));

Answer 2

if your class B looks something like this (where the MyList property of A looks for this FK)

public class B
{
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual A FK_ToA { get; set; }
}

then i think you are looking for (HQL)

nhSes.CreateQuery("select b.FK_ToA from B b where b.Date < :passedDate")
     .SetTimestamp("passedDate", DateTime.Now).List<A>()

Answer 3

I owe you the "elegant" part... :-)

This is a possible HQL. Note that inverted your condition: instead of looking for "A that have all the elements of their MyList property with a Date less than a given value", I look for "A that don't have any elements of their MyList property with a Date bigger than or equal to a given value".

from A a
where a not in 
      (select a1
       from A a1, B b
       where b.Date >= :date
       and   b in elements(a1.MyList))

Usage:

var results = session.CreateQuery("hql from above")
                     .SetParameter("date", DateTime.Today)
                     .List();

Note that, if you declare a bidirectional relationship between A and B (by adding an A property), the query is much simpler:

from A a
where a not in 
      (select b.A
       from B b
       where b.Date >= :date)

Update: here's how to do it with Criteria:

session.CreateCriteria<A>().Add(
    Subqueries.PropertyNotIn("id",
                             DetachedCriteria.For<A>()
                                 .CreateCriteria("MyList")
                                 .SetProjection(Projections.Property("id"))
                                 .Add(Restrictions.Ge("Date", DateTime.Today))))