4

Say I have two classes: Parent and Child. A Parent has a property Children, which is of course a collection of Child objects.

Child doesn't have a ParentId property. It does have a Parent property.

So, my NHibernate mapping for Child includes:

<many-to-one name="Parent" class="Parent" column="ParentId" cascade="save-update" />

And my Parent mapping includes:

<bag name="children" access="field" inverse="true" cascade="all-delete-orphan">
    <key column="ParentId" />
    <one-to-many class="Child" />
</bag>

Now here's what I want to do: I want to get all the Child objects with a certain ParentId. I know I can first get the Parent and then return its Children property. But what if I'd want to query the Child table directly?

If it would be a mapped property (for example, Name), I could use NHibernate's criteria, but in this case, ParentId isn't mapped.

I tried using something like:

criteria.Add(Restrictions.Eq("Parent.Id", 1));

But that doesn't work. I resorted to using SQLCriterion (as explained here), but a friend/colleague got me thinking there must be a better way.

Any ideas? Something with projections and Restrictions.EqProperty?

Peter
  • 13,733
  • 11
  • 75
  • 122

3 Answers3

6

You have to alias the association path. This will return a proxy for Parent assuming that are using lazy loads. You can access the parent's Id property without triggering a load.

return _session.CreateCriteria<Child>()
    .CreateAlias("Parent", "parent")
    .Add(Restrictions.Eq("parent.Id", parentId))
    .List<Child>();
Jamie Ide
  • 48,427
  • 16
  • 81
  • 117
  • This works, but joins to the Parent table and selects all the mapped columns. Cole W's answer doesn't, resulting in a cleaner SQL statement. That's why +1 for this answer, but Cole W's is marked as answer. – Peter Jan 11 '12 at 08:03
5

I've done this using query over. Here is an example: 

Child foundChild = 
    session.QueryOver<Child>()
        .Where(x => x.Parent.Id == 1234).SingleOrDefault<Child>();
Cole W
  • 15,123
  • 6
  • 51
  • 85
  • This gave me the cleanest SQL (see my comment on Jamie Ide's answer), although the difference isn't too big. – Peter Jan 11 '12 at 08:04
  • 1
    This will throw an exception if it finds more than one child for a parent. Change SingleOrDefault to List to return all the children of a parent. – Jamie Ide Jan 11 '12 at 12:46
  • True, I did use List: session.QueryOver().Where(x => x.Parent.Id == 1234).List() – Peter Jan 17 '12 at 07:47
0

I think it can be done via Criteria like this:

criteria.Add(Restrictions.Eq("Parent", Session.Load<Parent>(1));
Jochen
  • 1,488
  • 16
  • 21
  • Unfortunately, this gives me the following exception: Type mismatch in NHibernate.Criterion.SimpleExpression: Parent expected type MyDomain.Namespace.Parent, actual type Castle.Proxies.INHibernateProxyProxy_4. This could be specific to our project, YMMV. – Peter Jan 11 '12 at 08:02