How do I create a join in Fluent Nhibernate on two non-key properties whose names do not match?

I have two tables in my legacy database

Purchases

• Id int(PK)
• name varchar(50)
• MasterAccount char(10)
• BuyerAccount char(10)

MasterAccounts

• Id int(PK)
• Name varchar(50)
• MasterAccountNumber char(10)
• AccountNumber char(10)

I have an object that maps 1:1 to the purchases table. I want to add the "Name" column from the MasterAccounts table to the purchases object as a property.

How do I tell Fluent Nhibernate to perform a join when the two columns I want to join on:开发者_如何学C

1. are not defined as foreign keys in the database
2. Do not have the same name in each table

It might be helpful to see the SQL that I want to generate.

Select Purchases.*, MA.Name
from Purchases
left join MasterAccounts MA
on 
MA.MasterAccountNumber = Purchases.MasterAccount
and
MA.AccountNumber = Purchases.BuyerAccount

Assuming that the MasterAccount field in Purchases matches the MasterAccountNumber in MasterAccounts...create a view in sql with the table format you are trying to map:

CREATE VIEW [dbo].[v_PurchaseMasterAccountName]
AS
SELECT     dbo.Purchases.Id, dbo.Purchases.Name, dbo.Purchases.MasterAccount, dbo.Purchases.BuyerAccount, dbo.MasterAccounts.Name AS MasterAccountName
FROM         dbo.MasterAccounts INNER JOIN
                      dbo.Purchases ON dbo.MasterAccounts.MasterAccountNumber = dbo.Purchases.MasterAccount

Create a ClassMap to map your view:

public class Purchase
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string MasterAccountName { get; set; }
    public virtual string MasterAccount { get; set; }
    public virtual string BuyerAccount { get; set; }
}

public class PurchaseClassMap : ClassMap<Purchase>
{
    public PurchaseClassMap()
    {
        Table("v_PurchaseMasterAccountName");
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.MasterAccount);
        Map(x => x.BuyerAccount);
        Map(x => x.MasterAccountName);
    }
}

Ensure your ClassMap is picked up in your FluentMappings.

UPDATE:

This may work instead:

public class PurchaseMap : ClassMap<Purchase>
{
    public PurchaseMap()
    {
        Table("Purchases");
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.MasterAccount);
        Map(x => x.BuyerAccount);
        Map(x => x.MasterAccountName).ReadOnly()
            .Formula("(SELECT TOP 1 MasterAccounts.Name FROM MasterAccounts WHERE MasterAccounts.MasterAccountNumber = [MasterAccount] AND MasterAccounts.MasterAccountNumber = [BuyerAccount])");

    }
}

Thanks to Darren Kopp for the pointer to the Formula option.

This is kind of an unusual question and there may very well be other options, but here's my couple of ideas.

1. Use a formula. The property wouldn't be updateable but in your situation i think that's fine (mark property as read only).
2. I think you will have to do a hbm.xml file for this, but you can use your own query for the loader query in nhibernate.
3. Figure out what Subselect on the JoinPart does in fluent nhibernate? Doubt this will work but worth a try