Join multiple tables with NHibernate and QueryOver

Question

I have this tables:

Person -> PersonFavorites, PersonCompany
PersonCompany -> Company

I have now to do the following select with NHibernate and QueryOver:

select * from Person 
  inner join PersonFavorites on Person.Id = PersonFavorites.PersonId 
  inner join PersonCompany on Person.Id = PersonCompany.PersonId 
  inner join Company on Company.Id = PersonCompany.CompanyId
where ...

Can someone give me a sample, how I can do that? - My Problem is, that I have to join multiple Tables Person -> PersonCompany -> Company. The Join Person -> PersonCompany and Person -> PersonFavorites are no problem - but the next step from PersonCompany -> Company.

Greatest

Answer 1

Mapping should be similar to the following:

Entities

    class Person
    {
        ICollection<PersonFavorites> favorites;
        ICollection<PersonCompany> companies;
    }
    
    class PersonFavorite
    {
        Person person;
        Favorite favorite;
    }
    
    class PersonCompany
    {
        Person person;
        Company company;
    }

Query

    Person personAlias = null;
    PersonFavorite personFavoriteAlias = null;
    PersonCompany personCompanyAlias  = null;
    ICollection<PersonCompany> personCompaniesAlias = null;
    Company companyAlias = null;
    Favorite favoriteAlias = null;
    
    var query = Session.QueryOver<Person>(() => personAlias)
        .JoinAlias(() => personAlias.Favorites, () => personFavoriteAlias)
        .JoinAlias(() => personAlias.Companies, () => personCompanyAlias)
        // OR: .JoinAlias(() => personAlias.Companies, () => personCompaniesAlias)
        .JoinAlias(() => personfavoriteAlias.Favourite, () => favoriteAlias)
        .JoinAlias(() => personCompanyAlias.Company, () => companyAlias)
        .Where(() => personCompanyAlias.Person.Name == ...)
        // OR: .Where(() => personCompaniesAlias.Count() == ...)
        // ...
        ;

Swearingen

The aliases need to be initialized (example: Person personAlias = null) before they can be used. Otherwise, the code will throw an exception. – Bahadur

I tried to do something similar to this, but when I try to access personfavouriteAlias in the 3rd joinAlias, since it is a Collection, I do not have access to Favorite. Instead, I only have access to Collection properties like Count. – Tile

@Tile Actually, you can access both - the collection or a single entity of the collection via the join! The NHibernate API is really powerful here. I've added an example of that. – Semifinalist