Fluent NHibernate - Map 2 tables to one class

Question

I have two tables that I want to map to one class that will looks like:

CUSTOMER_INFO_CLASS.cs
----------------------
Id (CUSTOMER table) 
CustomerName (CUSTOMER table)
CustomerTypeDesc (CUSTOMER_TYPE table)

I tried to do it with join, as follows:

Table("CUSTOMER");

Id(x => x.ID).Length(10).Column("CustomerId");
Map(x => x.CustomerName);

Join("CUSTOMER_TYPE", m =>
    {
    m.Optional();
    m.Map(x => x.CustomerTypeDesc);
    m.KeyColumn("CustomerType");
    });

The problem is that the field with whom I'm trying to link the two tables is not a primary key in any of them. (And by default the join done by the field that defined as ID) So I found that for the CUSTOMER_TYPE table I can define the field by “KeyColumn”.
How can I define that the related column in the CUSTOMER table will be CustomerTypeCode and not CustomerId? (if I can at all)

At the end the sql query should looks like:

Select Id, CustomerName, CustomerAddress, CustomerTypeDesc
From CUSTOMER t1
  Left join CUSTOMER_TYPE t2
    On t1.CustomerTypeCode = t2.CustomerType

Jon Seigel Jon Seigel · Accepted Answer · 2010-01-05T15:28:42

If the Customer table maps the CustomerType member to the primary key of the CustomerType table, then Hibernate should do the join automatically for you.

Is there a reason why the CustomerType is not linked by a normal foreign key reference?

Fluent NHibernate - Map 2 tables to one class

1 Answers