nhibernatequeryovernhibernate-3

Join multiple tables with NHibernate and QueryOver


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.


Solution

  • 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() == ...)
            // ...
            ;