Join on tables using Fluent NHibernate

Question

I would like to make a join between tables using the FN. But the output I get is not quite correct result. My code:

public class Store
{
    public virtual int Id { get; protected set; }
    public virtual string Name { get; set; }
    public virtual IList<Product> Products { get; protected set; }
    public virtual IList<Employee> Staff { get; protected set; }
    public virtual string FirstName { get; set; }
}

public StoreMap()
{
    Table("store");
    Id(store => store.Id).Column("id").GeneratedBy.Increment();
    Map(store => store.Name).Column("name");

    Join("employee", m =>
    {
        m.Optional();
        m.KeyColumn("store_id");
        m.Map(x => x.FirstName).Column("first_name");
    });
}

var shops = session.QueryOver<Store>().Where(shop => shop.Id == 1).List();

SQL query that is generated

SELECT  this_.id as id3_0_, 
    this_.name as name3_0_, 
    this_1_.first_name as first2_0_0_ 
FROM store this_ 
left outer join employee 
    this_1_ on this_.id=this_1_.store_id 
WHERE this_.id == 1

If I just execute this SQL query, I get the correct result in the form

id3_0_          name3_0_        first2_0_0_
1               Bargin Basin    Daisy       
1               Bargin Basin    Jack
1               Bargin Basin    Sue

But if I am doing through FN, then the variable shops I get the following array:

1               Bargin Basin    Daisy       
1               Bargin Basin    Daisy
1               Bargin Basin    Daisy

I use the FN version 2.0.1.0, NHibernate 4.0. Thanks.

Answer 1

Join(...) is meant for a one-to-one association but you have a one-to-many to Employee so the join returns more that one row per store and NHibernate sees always the same Id which tells him it is the same object, hence it gives you 2 times the same reference.

What you probably want is a projection of employees with their stores

class EmploymentDto
{
    public int StoreId { get; set; }
    public string StoreName { get; set; }
    public string FirstName { get; set; }
}

EmploymentDto dto = null;
Employee employee = null;
var employments = session.QueryOver<Store>()
    .Where(shop => shop.Id == 1)
    .JoinAlias(s => s.Staff, () => employee)
    .SelectList(l =>
        l.Select(s => s.Id).WithAlias(() => dto.StoreId)
        l.Select(s => s.Name).WithAlias(() => dto.StoreName)
        l.Select(() => employee.FirstName).WithAlias(() => dto.FirstName)
    .TransformUsing(Transformers.AliasToBean<EmploymentDto>())
    .List<EmploymentDto>();