How to filter an item using one of its child set in nHibernate

I have an object that looks like the following ...

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping namespace="test" assembly="test" xmlns="urn:nhibernate-mapping-2.2">

<class name="Person" table="`Person`">
        <id name="ItemId" access="property" column="`ItemId`">
            <generator class="native" />
        </id>
        <property name="Name" column="`A`" />
        <property name="Age" column="`B`" />
        <set name="Email" inverse="true">
            <key column="`Name`" />
            <one-to-many class="EmailClass" />
        </set>
    </class>
</hibernate-mapping>

I would like to do a nHibernate query to filter these records by their email addresses

I've tried something along these lines ..

var childCriteria = QueryOver.Of<EmailClass>().Where(c => c.EmailAddress.ToString().IsLike("%" + strSearch + "%")).Select(c => c.EmailID);

var query = session.QueryOver<Person>().WithSubquery.WhereExists(childCriteria).Future();

I'm obviously doing something wrong. The question is, is it possible to search through the child set of a data object? or is it better deal for me to just searching the child table directly then find its parent?

Solution

You are almost there, I'd say.

I. some notes to mapping first...

Firstly, I assume that the EmailClass has relation to Person as well (because on DB level this is bidirectional relation).

public class EmailClass 
{
    ...
    public virtual Person Person { get; set; } // inverse mapping
}

That would be logical, because this DB relation is existing anyhow. It is already expressed as a Person's <set> named Email. And should be also must, because we used inverse="true", which expect bidirectional mapping...

Which also makes me to dispute the mapping. Are you sure, that this mapping is correct:

<class name="Person" ...
    // KEY column of this entity is ItemId (seems to be int)
    <id name="ItemId" column="`ItemId`" ...
    ...
    <set name="Email" inverse="true">
        // this mapping says: NHibernate, try to find the value
        // of the ItemId in the Person table
        // in the column Name of the table EmailClass ... ?
        <key column="`Name`" />
        ...
        // would expect
        <key column="ItemId" /> // column inside of EmailClass table

I am trying to say, usually, we can see, that the <id> column name used as a Key of a root entity (Person) is the same as the <key> column used for collection mapping.

So, I'd expect that EmailClass table should contain column like "ItemId" or "Person_ID" which would keep the reference to the Person table.

II. subquery filtering

Let's now continue with the query, expecting the above is true.

// An Alias, to be used later
Person person = null;

var childCriteria = QueryOver
    .Of<EmailClass>()
    // more QueryOver native style of a LIKE expression
    .WhereRestrictionOn(c => c.EmailAddress).IsLike(strSearch, MatchMode.Anywhere)
    // trick here
    // if we want to use the EXISTS later
    // we need to join outer and inner query here
    // and that's a place for outer query ALIAS
    .Where(c => c.PersonId == person.ItemId)
    .Select(c => c.PersonId); // must select something...

var query = session
    // ALIAS expressing the outer query in action again
    .QueryOver<Person>(() => person)
    .WithSubquery
    .WhereExists(childCriteria)
    .Future();

So, as we can see, almost there... Just a bit QueryOver-ish style for LIKE, and the essential WHERE clause Person = EmailAddress.Person

III. mapping ... suggestion

Let me, please, also provide some draft of the mapping I'd expect/suggest:

public class Person
{
    public virtual int ItemId { get; set;}
    public virtual IList<EmailClass> Emails { get; set; } // plural Emails
}
public class EmailClass 
{
    ...
    public virtual Person Person { get; set; } // inverse mapping
}

mapping:

<class name="Person" ...
    ...
    <set name="Emails"        // plural
      cascade="all-delete-orphan" // usually makes sense to rely on NHibernate cascade
      batch-size="25"         // great feature improving 1 + N issue
      inverse="true"  >       // already used, improves WRITE operations sequence
      <key column="ItemId" /> // ItemId should column in the EmailClass table
        <one-to-many class="EmailClass" />
    </set>
</class>
<class name="EmailClass">
   <id ... 
   <many-to-one name="Person" column="ItemId" /> // the same column as in above <set
   ...