Join on tables using Fluent NHibernate

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.

Answers


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>();

Need Your Help

Nvprof, metrics, the elapsed_cycles variable

cuda profiler

I'm having some issues with the CUDA nvprof profiler. Some of the metrics on the site are named differently than in the profiler, and the variables don't seem to be explained anywhere on the site, ...

load asp.net grid view from asynchronous call

asp.net json gridview asynchronous

I have a web page which take several minutes to load. The reason for this was this page consist an ASP.NET grid view and data binding for this grid take lots of time.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.