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

i have 2 tables in 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 object maps 1:1 purchases table. want add "name" column masteraccounts table purchases object property.

how tell fluent nhibernate perform join when 2 columns want join on:

1. are not defined foreign keys in database
2. do not have same name in each table

it might helpful see sql want generate.

 select purchases.*, ma.name purchases left join masteraccounts ma on  ma.masteraccountnumber = purchases.masteraccount , ma.accountnumber = purchases.buyeraccount 

assuming masteraccount field in purchases matches masteraccountnumber in masteraccounts...create view in sql table format trying map:

create view [dbo].[v_purchasemasteraccountname] select     dbo.purchases.id, dbo.purchases.name, dbo.purchases.masteraccount, dbo.purchases.buyeraccount, dbo.masteraccounts.name masteraccountname         dbo.masteraccounts inner join                       dbo.purchases on dbo.masteraccounts.masteraccountnumber = dbo.purchases.masteraccount 

create classmap map 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 classmap picked in 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 masteraccounts masteraccounts.masteraccountnumber = [masteraccount] , masteraccounts.masteraccountnumber = [buyeraccount])");      } } 

thanks darren kopp pointer formula option.