CODEWITHSUNDEEP
nhibernate
AndySavage
AndySavage

Reputation: 1769

NHibernate Left Join with condition or null

I have 2 objects, Project and UsageLast30 mapped as one-to-one.

I am trying to query for all Projects which either don't have a UsageLast30 yet, or have a UsageLast30 with a date property before a threshold.

I get a null reference exception if I use the query:

session.QueryOver<Project>(() => p)
    .Left.JoinAlias(() => p.UsageLast30, () => u)
    .Where(() => u == null || u.LastCalculated < threshold)

The following alternative runs, but doesn't not produce the SQL I would expect.

session.QueryOver<Project>(() => p)
    .Left.JoinAlias(() => p.UsageLast30, () => u)
    .Where(Restrictions.Or(
        Restrictions.Where<Project>(x => x.UsageLast30 == null),
        Restrictions.Where(() => u.LastCalculated < threshold)
    ))

This gives a WHERE condition of:

WHERE  (this_.project_id is null
     or u1_.last_calculated < '2015-09-08T18:18:51' /* @p0 */)

The null test is on Project, not on UsageLast30 as I would expect.

How can I build this query?

Mapping as follows:

<class name="Analytics.Core.Project" table="project">
    <id name="Id" column="project_id">
      <generator class="identity" />
    </id>
    <!-- ... -->
    <one-to-one name="UsageLast30" constrained="false" foreign-key="none" />
</class>

<class name="Analytics.Core.ProjectUsageLast30" table="project_usage30">
    <id name="ProjectId" column="project_id">
        <generator class="foreign">
            <param name="property">Project</param>
        </generator>
    </id>
    <!-- ... -->
    <one-to-one name="Project" constrained="true" />
</class>

Upvotes: 3

Views: 1810

Answers (1)

AndySavage
AndySavage

Reputation: 1769

I still don't know why testing for null on p.UsageLast30 generates the wrong null test, but testing for a null property rather than a null object gives the correct SQL query. For example:

session.QueryOver<Project>(() => p)
    .Left.JoinAlias(() => p.UsageLast30, () => u)
    .Where(() => u.LastCalculated == null || u.LastCalculated < threshold)

Notice the u.LastCalculated == null compared to u == null

Upvotes: 3

Related Questions