Querying across relationships with NHibernate.Linq

[Nick Aceves]

We've recently upgraded one of our projects to NH 2.1, and would like
to use NHibernate.Linq for as much of our querying as possible.

We have several queries of the following form:

session.CreateCriteria(typeof(Parent))
.CreateCriteria("Children")
.Add(Restrictions.Eq("PropertyOfChild", someValue))

We are using CreateCritera calls (sometimes more than one) to query
across an entity's relationships. This works very well when the
properties are one-to-many or many-to-many relationships. The SQL this
generates (as seen through NHProf) is almost identical to the SQL I
would write by hand. It does an inner join across each relationship,
and then filters the results in the where clause. Simple as cake.

There appears to be no way to get NHibernate.Linq to do this. I've
tried all of the following:

/* Works, but generates a subquery. This quickly gets out of control
with multiple relationships, or when querying anything beyond direct
children (e.g., grandchildren). */
from parent in session.Linq<Parent>()
where parent.Children.Any(c => c.PropertyOfChild == someValue)
select parent;

/* Does an inner join to get to Children, but the joined table isn't
used to filter the results. Instead, a subquery is still generated. */
from parent in session.Linq<Parent>().Expand("Children")
where parent.Children.Any(c => c.PropertyOfChild == someValue)
select parent;

Am I missing something, or is there just no way to do this using the
current iteration of NHibernate.Linq?

-Nick

[Chris Nicola]

What sql is it generating for you?  I ran a similar query on my own project to compare and it resulted it a single select statment:

      var query = from entry in Session.Linq<Entry>()
                  where entry.EntrySplits.Any(x => x.Amount > 100)
                  select entry;
      query.ToList();

(I won't post the sql as I don't currently have pretty nhprof formatted output available to me)

This should work but perhaps it has something to do with how your collection is map.  Here is my HBM file:

The Entry side:

    <bag name="EntrySplits" inverse="true" cascade="save-update,delete-orphan">
      <key column="idEntry" on-delete="cascade" />
      <one-to-many class="AccountingManager.Model.EntrySplit,AccountingManager.Model" />
    </bag>

The EntrySplit side:

<many-to-one name="Entry" index="entry_index" class="AccountingManager.Model.Entry,AccountingManager.Model" column="idEntry" cascade="none" not-null="true" />

As you can see I have an inverse relationship set up so perhaps that is the reason.  Although, I can't think why the Criteria query is producing a different result that NHLinq.  Make sure you are running the latest version http://ayende.com/Blog/archive/2009/07/26/nhibernate-linq-1.0-released.aspx

Chris