[FIXED] Add Restrictions to a join using CreateCriteria in nHibernate

Issue

Beginner question I'm sure:

I am trying to do a join between 2 tables and restrict the result at the same time like this:

var bookings = session.CreateCriteria<Booking>("p")
                       .CreateCriteria("p.BookingLocations", JoinType.InnerJoin)
                       .Add(Restrictions.Eq("p.BookingLocations.locationID", locationId))
                       .SetMaxResults(30)
                       .List<Booking>();

I get the error: Could not resolve property: BookingLocations.locationID of: Booking

I can see that Booking.BookingLocation contains many records at there is a one to many relation ship between Booking and BookingLocation, but I'm not sure if that's the cause of the problem.

I guess if it was I'd need to do something like:

.Add(Restrictions.Eq("p.BookingLocations.first().locationID", locationId))

...but doubtlessly that wouldn't work ;)

Classes

public class Booking
{
    public virtual int Id { get; set; }
    public virtual Int32 bookingID { get; set; }
    public virtual Int32 bookingAdminID { get; set; 
}

public class BookingLocation
{
    public virtual int Id { get; set; }
    public virtual Int32 bookingID { get; set; }
    public virtual Int32 locationID { get; set; } 
}

Mappings

 public BookingMap()
 {
    Table("Bookings");

    Id(x => x.Id).Column("ID");
    Map(x => x.bookingID).Column("BookingID");
    Map(x => x.bookingAdminID).Column("BookingAdminID");
 }

public class BookingLocation
{
    public virtual int Id { get; set; }
    public virtual Int32 bookingID { get; set; }
    public virtual Int32 locationID { get; set; } 
 }

Solution

The subcriteria defines a new scope, which is the rooted at BookingLocation. You just need to use locationID:

session.CreateCriteria<Booking>("p")
       .CreateCriteria("p.BookingLocations", JoinType.InnerJoin)
       .Add(Restrictions.Eq("locationID", locationId));

Answered By - JB Nizet
Answer Checked By - Timothy Miller (JavaFixing Admin)