Fluent NHibernate Join with Constraint

Question

i have an entity with its properities spread over two tables that i'd like to map to one class using Fluent NHibernate, but with a constraint on the joining table.

i've changed the domain of my problem for this question to be the familar 'customer' domain, so my example here may seam a little contrived, but it illustrates my problem. it's bascially this; i have a Customer table that has some customer attributes in it, but the first and last names of the customer are held in a separate CustomerName table as two rows linked to the customer and identified as first and last names.

the following is the table schema:

CREATE TABLE Customer( CustomerId int, Birthday datetime )

CREATE TABLE CustomerName( CustomerId int NOT NULL, CustomerNameTypeId int NOT NULL, Name nvarchar(25) NOT NULL )

CREATE TABLE CustomerNameTypes( CustomerNameTypeId NOT NULL, Description nvarchar(25) NOT NULL )

with the CustomerNameTypes table containing two rows: 1, "FirstName" 2, "SecondName"

what i need is a Fluent Mapping that will map the above to the following:

public class Customer
{
    public virtual int CustomerId { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual DateTime Birthday { get; set; }
}

can anyone help?!

many thanks in advance Chris Browne

Answer 1

just thought i'd add, this is the equivalent sql:

select c.CustomerId, c.Birthday, fn.Name, ln.Name
from Customer c
  left join CustomerName fn on c.CustomerId = fn.CustomerId and fn.CustomerNameTypeId = (select CustomerNameTypeId from CustomerNameTypes where Description = 'First')
  left join CustomerName ln on c.CustomerId = ln.CustomerId and ln.CustomerNameTypeId = (select CustomerNameTypeId from CustomerNameTypes where Description = 'Last')

cheers!

Answer 2

Can't really answer this question with nHibernate, but I have few other options that might help you solve it!

1. Personally I would re-write the sql as follows:

   select 
         c.CustomerId, 
         c.Birthday, 
         fn.Name, 
         ln.Name
   from Customer c
   left join CustomerName fn on c.CustomerId = fn.CustomerId
   left join CustomerNameTypes fnt on fnt.CustomerNameTypeId = fn.CustomerNameTypeId
         and fnt.[Description] = 'First'
   left join CustomerName ln on c.CustomerId = ln.CustomerId 
   left join CustomerNameTypes lnt on lnt.CustomerNameTypeId = ln.CustomerNameTypeId
         and lnt.[Description] = 'Last'
   

   This might help you solve while still using NHibernate.

2. The other option is to use a view. I personally use LLBLGenPro and before Linq was available in v2.6 it was my favoured method of solving hard queries quickly and easily.

3. This is going to be a hard one I'd imagine, especially if you have a stubborn DBA or it is a third-party DB!! De-normalise the database. This structure is far too normalised and is the whole reason you are getting into this problem.

Anyway I know it's not the answer to your problem specifically but I hope you will get some use out of it!! :)

Answer 3

I know this doesn't quite answer what you were asking, but...

If both LastName and FirstName were on the same row of the CustomerName table (but they're not), you could accomplish this with the <join> mapping. Ayende describes this HBM mapping on his blog. There is a fluent-nhibernate example of this here: Entity spanning multiple tables. The method name has since changed from WithTable() to Join(). Here's what it would look like:

public class CustomerMap : ClassMap<Customer>
{
    public CustomerMap()
    {
        Id(x => x.CustomerId);
        Join("CustomerName", m =>
        {
            m.Map(x => x.FirstName);
            m.Map(x => x.LastName);
        });
        // ... other properties ...
    }
}

I'm not sure yet how to address the problem of the two names being on separate rows.