Fetch entire entity with joined entities from database and avoid lazy load, nHibernate QueryOver

Question

I have an entity like this:

public class Employment
{
    public virtual Company Company {get; set;}
    public virtual Person Person {get; set;}
    public virtual string Description {get; set;}
}

providing a relationship between two other entities. They have corresponding DTO's and I want to return a result set containing all information on persons and companies. The query is performed on the Employment table and my problem is that Hibernate generates one select statement per company and person.

In my database, The Employment table has 1000 rows. Nhibernate generates 2001 select statements, one for the Employment list and one select for each Person and Company as I map them to DTO's.

I would like hibernate to fetch all information at once, in SQL I would have done something like this:

SELECT e.Description, c.A, c.B, c.C, p.D, p.E, p.F
FROM Employment e
JOIN Company c ON e.Company_Id = c.Company_Id
JOIN Person p ON e.Person_Id = p.Person_Id;

Or even

SELECT Description FROM Employment;

SELECT c.A, c.B, c.C FROM Employment e
JOIN Company c ON e.Company_Id = c.Company_Id;

SELECT p.D, p.E, p.F FROM Employment e
JOIN Person p ON e.Person_Id = p.Person_Id;

I am a pretty fresh user of nHibernate, QueryOver.I welcome Linq-To-Entities answers as well but I prefer to avoid LINQ query expressions.

I've looked all over the web, read about JoinQuery, JoinAlias and Fetch and have come as far as something like this:

//This works, but the objects are retrieved as PersonProxy and CompanyProxy,
//generating 2 SELECT statements for each Employment I map to EmploymentDto
var queryOver =
    session.QueryOver<Employment>()
    .Fetch(x => x.Person).Eager
    .Fetch(x => x.Company).Eager
var mapResult = MappingEngine.Map<IList<EmploymentDto>>(queryOver.List());    


//This works, but the objects are still retrieved as PersonProxy and CompanyProxy,
var queryOver =
    session.QueryOver<Employment>()
        .JoinAlias(x => x.Person, () => personAlias, JoinType.InnerJoin)
        .JoinAlias(x => x.Company, () => companyAlias, JoinType.InnerJoin);
var mapResult = MappingEngine.Map<IList<EmploymentDto>>(queryOver.List());

JoinQuery provided the same result aswell. I feel I am missing something important here. Something should be done in the query or before .List() to fetch all child entities instead of loading a list with lots of Employment entities loaded with PersonProxy and CompanyProxy. I can, however, not find out how...

EDIT: Added mapping

Database tables:

TABLE Company(
Id,
A,
B,
C)

TABLE Person(
Id,
D,
E,
F);

TABLE Employment(
Person_Id,
Company_Id,
Description);

Entities

public class Company
{
    public virtual string Id { get; set; }
    public virtual string A { get; set; }
    public virtual bool B { get; set; }
    public virtual bool C { get; set; }
}

public class Person
{
    public virtual string Id { get; set; }
    public virtual string D { get; set; }
    public virtual string E { get; set; }
    public virtual string F { get; set; }
}

public class Employment
{
    public virtual Person Person { get; set; }
    public virtual Company Company { get; set; }
    public virtual string Description { get; set; }

    public override bool Equals(object obj)
    {
        Employment toCompare = obj as Employment;
        if (toCompare == null)
            return false;
        return (this.GetHashCode() != toCompare.GetHashCode());
    }

    public override int GetHashCode()
    {
        unchecked
        {
            int results = Person != null ? Person.GetHashCode() : 0;
            results = (results * 397) ^ (Company != null ? Company.GetHashCode() : 0);
            results = (results * 397) ^ (Description != null ? Description.GetHashCode() : 0);
            return results;
        }
    }
}

Mapping

public class CompanyMap : SyncableClassMap<Company>
{
    public CompanyMap()
    {
        Table("Company");
        Id(x => x.Id).Column("Id").GeneratedBy.Assigned();
        Map(x => x.A).Column("A");
        Map(x => x.B).Column("B").CustomType<YesNoType>();
        Map(x => x.C).Column("C").CustomType<YesNoType>();
    }
}

public class PersonMap : SyncableClassMap<Person>
{
    public PersonMap()
    {
        Table("Person");
        Id(x => x.Id).Column("Id").GeneratedBy.Assigned();
        Map(x => x.D).Column("D");
        Map(x => x.E).Column("E");
        Map(x => x.F).Column("F");
    }
}

public class EmploymentMap : ClassMap<Employment>
{
    public EmploymentMap()
    {
        Table("Employment");
        CompositeId()
            .KeyReference(x => x.Person, "Person_Id")
            .KeyReference(x => x.Company, "Company_Id");
        Map(x => x.Description, "Description");
    }
}

Answer 1

after your edit i see you have a keyreference instead of a normal many-to-one.

Unfortunatly this seems to be a limitation of QueryOver/Criteria which does not eager load keyreferences even with Fetchmode specified. However Linq to NH does not have this limitation. Change the query to

using NHibernate.Linq;

var results = session.Query<Employment>()
    .Fetch(x => x.Person)
    .Fetch(x => x.Company)
    .ToList();