Search code examples
joinnhibernatecriteriaqueryover

Joining one column to multiple tables using Critera or QueryOver API

A Route consists of Waypoints. One Waypoint can exist on multiple routes. A Route can be serviced by multiple Salesmen.

To obtain the Waypoints a salesman needs to visit, I can use this (simplified) SQL:

  SELECT WP.*
  FROM Waypoint WP
  JOIN RouteWaypoint RWP
    ON RWP.RouteId = WP.RouteId
  JOIN Route R
    ON RWP.RouteId = R.RouteId
  JOIN RouteSalesMan RMS
      ON RMS.RouteId = R.RouteId
  WHERE RMS.SalesManId = 5
  AND R.Status = 1 -- Only active routes

How can I generate this (or similar) SQL using the Criteria or QueryOver API in NHibernate?

I've tried various approaches but I can't seem to join Waypoint to RouteWaypoint and to RouteSalesMan. NHibernate won't let me associate a field more than once when I define the aliases, and when I try to join route.RouteId to anything it complains that it's not an association. If I don't specify the field, it complains that it cannot find the field "Route" on Waypoint.

Solution

  • It turns out there was nothing wrong with how my QueryOver/Criteria calls were structured. The problem that the Fluent Nhibernate mappings were not generated correctly.

    The Composite Ids were create as:

       CompositeId().KeyProperty(x => x.SalesManId, "SalesManId")
                ... // Details elided;
   References(x => x.SalesMan).Column("SalesManId);

    Instead of 

       CompositeId().KeyReference(x => x.SalesManId, "SalesManId")...