Joining one column to multiple tables using Critera or QueryOver API

Question

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.

Subquery is the way to go. Check [this](http://stackoverflow.com/q/20528760/1679310) or [that](http://stackoverflow.com/a/21139352/1679310) or [here](http://stackoverflow.com/q/23772548/1679310) — Radim Köhler, Oct 29 '15 at 15:04

score 0 · Accepted Answer · answered Oct 29 '15 at 19:07

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")...

Joining one column to multiple tables using Critera or QueryOver API

1 Answers1