NHibernate Linq property-ref behavior

Question

NHibernate Linq is producing an unexcepted query. Here are my class mappings:

public class OrderClassMap : ClassMap<Order> {
    public OrderClassMap() {
        Id(x => x.PersistenceId).Column("RowId").GeneratedBy.Assigned();
        Version(x => x.PersistenceVersion).Column("RowVersion");
        Map(x => x.OrderId).Generated.Insert().Not.Update();
        Map(x => x.OrderDate);
        References(x => x.Overseer).Column("OverseerId").PropertyRef(x => x.OverseerId);
        References(x => x.Overseer2).Column("OverseerId2").PropertyRef(x => x.OverseerId2);
    }
}

public class OverseerClassMap : ClassMap<Overseer> {
    public OverseerClassMap() {
        Id(x => x.PersistenceId).Column("RowId").Generated.Assigned();
        Version(x => x.PersistenceVersion).Column("RowVersion");
        Map(x => x.OverseerId).Generated.Insert().Not.Update();
        Map(x => x.Name);
        Map(x => x.Email);
    }
}

Here is the Linq query I have:

var orders = session.Query<Order>()
                    .Where(o => ((o.Overseer.OverseerId == 1) || (o.Overseer.OverseerId == 2)))
                    .Fetch(o => o.Overseer)
                    .Fetch(o => o.Overseer2);

The SQL query that is generated is not what I would expect. Instead of doing a left outer join, it does a cartesian join. Here is a shortened example of the query NHibernate produces:

SELECT ...
FROM Orders o LEFT OUTER JOIN
     Overseers v1 ON o.OverseerId = v1.OverseerId LEFT OUTER JOIN
     Overseers v2 ON o.OverseerId2 = v1.OverseerId,
     Overseers v3,
     Overseers v4
WHERE o.OverseerId = v3.OverseerId AND
      o.OverseerId2 = v4.OverseerId AND
      (v3.OverseerId = 1 OR v4.OverseerId = 1)

The query I expected would be like:

SELECT ...
FROM Orders o LEFT OUTER JOIN
     Overseers v1 ON o.OverseerId = v1.OverseerId LEFT OUTER JOIN
     Overseers v2 ON o.OverseerId2 = v1.OverseerId
WHERE (v1.OverseerId = 1 OR v2.OverseerId = 1)

I think it is doing this because I have a legacy database and am using PropertyRef in my mapping. Is there any way to fix this unexpected behavior? Why does NHibernate behave this way? It doesn't make sense to me because by specifying PropertyRef, I'm saying "Use this key instead of the primary key".

Same thing, only a separate select is issued for each Overseer when I access their properties (lazy loading). — awilinsk
Did you see the hbm mappings FNH generates? are they built correctly? — Variant
They are built correctly. When I use the QueryOver api instead of linq, it works as expected, but the QueryOver api has issues with sorting by nested properties (ie: can't sort by "Overseer.Name"). — awilinsk

Chen Kinnrot Chen Kinnrot · Accepted Answer · 2011-06-02T13:15:47

Try using QueryOver<>, and specify the left outer join.

NHibernate Linq property-ref behavior

2 Answers