NHibernate Cross Join: The multi-part identifier … could not be bound..

Something else I found out about NHibernate, at least an older version, and cross joins… and if you’re reading this chances are you have the same version. Say you want to cross join something but there are also other joins. You would think it should be like this:

    SELECT
       user.UserName, status.StatusName
    FROM
       User user, UserStatus status
    LEFT JOIN
      user.Address address
    WHERE
      user.CreatedDate = status.CreatedDate

Ok so once again, this query is kind of stupid but the idea is there. With NHibernate you have to Cross Join when you need to match two objects but not on the mapped keys. Meaning that User and Status might be mapped on UserID ie Status.UserID = User.UserID. But what if you wanted to match them on something else, like a date. Well that’s where the cross join came in. The Sql might look like:

    SELECT
       user0_.UserName, status0_.StatusName
    FROM
       ByATool.User user0_, ByATool.UserStatus status0_
    LEFT JOIN
       ByATool.Address address0_ ON address0_.UserID = user0_.UserID
    WHERE
       user0_.CreatedDate = status0_CreatedDate

Looks fine right? Well it’s not according to SqlServer 2005. Turns out the cross join has to appear AFTER the other joins. So in reality it should look like:

    SELECT
       user0_.UserName
    FROM
       ByATool.User user0_, ByATool.UserStatus status0_
    LEFT JOIN
       ByATool.Address address0_ ON address0_.UserID = user0_.UserID,
       status0_.StatusName
    WHERE
       user0_.CreatedDate = status0_CreatedDate

Which means the hql looks like this:

    SELECT
       user.UserName
    FROM
       User user, UserStatus status
    LEFT JOIN
      user.Address address,
      status.StatusName
   WHERE
      user.CreatedDate = status.CreatedDate

See how the cross joined object is now after the Left Join? Looks odd but this is what it takes to get the cross join through. Kind of annoying.