Fluent Nhibernate Mapping Multiple Join

Question

I am somewhat of an Nhibernate newbie and I have been researching but am unable to figure out how to accomplish what I want using an Nhibernate mapping.

I have the following tables for my objects

Domains
Companies
Dealers

Domains can have many Companies and Companies can belong to many Domains. Companies have many Dealers but Dealers only belong to one Company. I would like a mapping for my Domains to generate a list of all the Dealers that belong to it, via the Companies.

I have a join table called CompanyDomains (with columns CompanyID and DomainID) that maintains a many-to-many relationship between Companies and Domains. This table is currently NOT mapped in my Nhibernate setup...I just join on it with a HasManyToMany in my Domain map. The Dealers table has a column with the CompanyID.

The results I want are easily attained with an SQL query:

SELECT * FROM Dealers
JOIN Companies on Companies.ID = Dealers.CompanyID
JOIN CompanyDomains on Companies.ID = CompanyDomains.CompanyID
WHERE DomainID = 1

This gives me all Dealers assigned to the Domain.

My question is, I want a List of Dealers in my Domain Mapping for this. How do I map that with Nhibernate?

If I have overlooked this solution elsewhere, please point me in the right direction, otherwise I appreciate any help you can provide.

Thanks! Erick

ChrisWue ChrisWue · Accepted Answer · 2012-01-10T18:52:09

A possible solution would be to map a Dealers collection for a company with HasMany and add a convenience property to Domain:

public class Domain
{
    ...
    public virtual IList<Company> Companies { get; private set; }
    public IList<Dealer> Dealers 
    { 
        get { return Companies.SelectMany(x => x.Dealers).Distinct(); }
    }
}

public class Company
{
    ...
    public virtual IList<Domain> Domains { get; private set; }
    public virtual IList<Dealer> Dealers { get; private set; } 
}

public class Dealer
{
    ...
    public virtual Company Company { get; private set; }
}

As there is no immediate relationship between Dealer and Domain I don't think you can map it directly.

Fluent Nhibernate Mapping Multiple Join

1 Answers