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

Question

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?

Diego Mijelshon · Accepted Answer · 2010-06-29T11:28:12.357

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))))

good point for the bidirectional relationship: I'll add a property of type A in my class B. HQL is great but I was wondering if we couldn't get sth with DetachedCriteria() and Projections.Max() methods — pierroz, Jun 29 '10 at 07:28
OK, I added the Criteria option (it wouldn't be hard to take that and change it to use `Subqueries.PropertyIn` and invert the subquery to use a projection). However, Criteria is more useful for dynamically constructed queries (search). Look at all the noise, compared to the HQL. — Diego Mijelshon, Jun 29 '10 at 11:31

score 0 · Answer 2 · answered Jun 28 '10 at 17:40

0

Use this

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

answered Jun 28 '10 at 17:40

That query does NOT return what PierrOz is expecting. – Diego Mijelshon Jun 28 '10 at 20:16

score 0 · Answer 3 · answered Jun 28 '10 at 20:59

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>()

score -1 · Answer 4 · answered Jul 20 '11 at 12:27

-1

The currently accepted answer relies on a correlated sub-query, which as a rule-of-thumb is just "bad SQL".

It's much better to simply express this using set based semantics rather than a more functional approach.

Essentially you want your SQL to look like this:

SELECT
 A.Id
FROM A
 LEFT OUTER JOIN B ON A.Id = B.FKtoA
WHERE B.Date < @MyDate

This reads as "I want a set of columns from A as related to a set of B where B's Date is less than some value". This can be achieved using the ICriteria API:

ICriteria criteria =  session.CreateCriteria<A>();
criteria.CreateAlias("MyList", "b", JoinType.LeftOuterJoin)
criteria.Add(Restrictions.Lt("b.Date", myDate));
criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());
criteria.List<A>();

Part of the trick is using NHibernate's built-in DistinctRootEntityResultTransformer: since the left outer join could return multiple instances of A per B, we want our ICriteria to only return the distinct instances (assuming we don't care about ordering or whatever else).

answered Jul 20 '11 at 12:27

Joseph Daigle

47,650
10
49
73

I suggest that you back your "rules of thumb" with some hard facts. Plus, your suggested SQL wouldn't work: using B in the WHERE statement makes it an inner join (the same happens with your Criteria, which has the additional burden of the client-side distinct transformer) – Diego Mijelshon Jul 20 '11 at 17:20
It would NOT cause an inner join as I've specified it as an outer-join. You can verify this via a profiler. Essentially there are two approaches to the problem: a single query (which is the solution I've suggested) or two queries (either via a correlated sub-query or a two completely separate queries). Both have pros/cons. You've mentioned the primary disadvantage to a single query. In the case of the correlated sub-query, check this out: http://stackoverflow.com/questions/141278/subqueries-vs-joins – Joseph Daigle Jul 20 '11 at 23:22
A filter outside the JOIN condition turns it into an inner join (*try it*). You can make it part of the join condition with HQL (using the WITH clause) but not with Criteria. Also: the subquery in this case is NOT correlated (I'm not using elements of the outer query inside the inner one), so it's equivalent to a join, at least with decent DB engines. – Diego Mijelshon Jul 20 '11 at 23:31
Okay. You were right about the JOIN. I hadn't actually looked at the actual query plans until now (I should have). Although, interestingly, the query plan for both types of queries (join or subquery) ended up with the exact same query plan: both with a little bit of data and with a lot of data. – Joseph Daigle Jul 21 '11 at 00:18

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

4 Answers4