Nhibernate query with null child

Question

I need to retrieve all the users with a valid Wish property (so not null). This is the xml of my class:

<class name="Project.Engine.Domain.User,Project.Engine" table="Users" lazy="true">
  <id name="UserID" column="UserID">
    <generator class="native" />
  </id>
  <property name="Firstname" column="Firstname" type="string" not-null="true"
    length="255" />
  <property name="Lastname" column="Lastname" type="string" not-null="true"
    length="255" />
  <property name="Email" column="Email" type="string" not-null="true"
    length="255" />
  <one-to-one name="Wish" cascade="all" property-ref="UserID"
    class="Project.Engine.Domain.Wish, Project.Engine"  />
</class>

The method to get all my users is the following:

public PagedList<User> GetAll(int pageIndex, int pageSize,
    string orderBy, string orderByAscOrDesc)
{
    using (ISession session = NHibernateHelper.OpenSession())
    {
        var users = session.CreateCriteria(typeof(User));
        users.Add(Restrictions.IsNotNull("Wish"));
        return users.PagedList<User>(session, pageIndex, pageSize);
    }
}

As you can notice, I have added the Restriction on the child object. This doesn't work properly as the method return all users including the ones with Wish property as null. Any help?

this is the xml for child:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Project.Engine.Domain.Wish,Project.Engine" table="Wish" lazy="false">
    <id name="WishID" column="WishID">
      <generator class="native" />
    </id>
    <property name="UserID" column="UserID" type="int" not-null="true" length="32" />
    <property name="ContentText" column="ContentText" type="string" not-null="false" length="500" />
    <property name="Views" column="Views" type="int" not-null="true" length="32" />
    <property name="DateEntry" column="DateEntry" type="datetime" not-null="true" />
  </class>
</hibernate-mapping>   

Answer 1

Well, there is a bug with one-to-one and null testing of the side which may not exist. I had already encountered it but forgot about it. The property-ref just render it a bit more tricky to diagnose, but it does exist on actual one-to-one too.

Here is its corresponding issue in NHibernate tracking tool.

Workaround: test for null state of an non-nullable property of Wish, like Wish.Views.

Forgive the wild guess on test syntax, I do not use nhibernate-criteria anymore since years, but try by example:

public PagedList<User> GetAll(int pageIndex, int pageSize,
    string orderBy, string orderByAscOrDesc)
{
    using (ISession session = NHibernateHelper.OpenSession())
    {
        var users = session.CreateCriteria(typeof(User));
        users.Add(Restrictions.IsNotNull("Wish.Views"));
        return users.PagedList<User>(session, pageIndex, pageSize);
    }
}

Using linq-to-nhibernate, I confirm this workaround works with my own projects, which gives by example:

// The "TotalAmount != null" seems to never be able to come false from a 
// .Net run-time view, but converted to SQL, yes it can, if TransactionRecord
// does not exist.
// Beware, we may try "o.TransactionsRecord != null", but you would get struck
// by https://nhibernate.jira.com/browse/NH-3117 bug.
return q.Where(o => o.TransactionsRecord.TotalAmount != null);

I maintain my other answer since you may consider using a many-to-one instead, especially since you do not have made a bidirectionnal mapping (no corresponding constrained one-to-one in Wish) in addition to not having an actual one-to-one. many-to-one does not suffer of the bug.

Answer 2

one-to-one mapping using property-ref is not an "actual" one-to-one, and usually this is a sign a many-to-one mapping should be used instead.
Maybe this is not related to your trouble, but you may give it a try.

An "actual" one-to-one has the dependent table primary key equals to the parent table primary key. (Dependent table, Wish in your case, would have a foreign primary key, UserId in your case. See this example.)

^{I have sometime "played" with 'one-to-one property-ref', and I always ended giving it up due to many issues. I replaced that with more classical mappings, either changing my db for having an actual one-to-one, or using a many-to-one and living with a collection on child side though it would always contain a single element.}