FluentNHibernate Many-To-One References where Foreign Key is not to Primary Key and column names are
- by Todd Langdon
I've been sitting here for an hour trying to figure this out...
I've got 2 tables (abbreviated):
CREATE TABLE TRUST
(
TRUSTID NUMBER NOT NULL,
ACCTNBR VARCHAR(25) NOT NULL
)
CONSTRAINT TRUST_PK PRIMARY KEY (TRUSTID)
CREATE TABLE ACCOUNTHISTORY
(
ID NUMBER NOT NULL,
ACCOUNTNUMBER VARCHAR(25) NOT NULL,
TRANSAMT NUMBER(38,2) NOT NULL
POSTINGDATE DATE NOT NULL
)
CONSTRAINT ACCOUNTHISTORY_PK PRIMARY KEY (ID)
I have 2 classes that essentially mirror these:
public class Trust
{
public virtual int Id {get; set;}
public virtual string AccountNumber { get; set; }
}
public class AccountHistory
{
public virtual int Id { get; set; }
public virtual Trust Trust {get; set;}
public virtual DateTime PostingDate { get; set; }
public virtual decimal IncomeAmount { get; set; }
}
How do I do the many-to-one mapping in FluentNHibernate to get the AccountHistory to have a Trust? Specifically, since it is related on a different column than the Trust primary key of TRUSTID and the column it is referencing is also named differently (ACCTNBR vs. ACCOUNTNUMBER)???? Here's what I have so far - how do I do the References on the AccountHistoryMap to Trust???
public class TrustMap : ClassMap<Trust>
{
public TrustMap()
{
Table("TRUST");
Id(x => x.Id).Column("TRUSTID");
Map(x => x.AccountNumber).Column("ACCTNBR");
}
}
public class AccountHistoryMap : ClassMap<AccountHistory>
{
public AccountHistoryMap()
{
Table("TRUSTACCTGHISTORY");
Id (x=>x.Id).Column("ID");
References<Trust>(x => x.Trust).Column("ACCOUNTNUMBER").ForeignKey("ACCTNBR").Fetch.Join();
Map(x => x.PostingDate).Column("POSTINGDATE");
);
I've tried a few different variations of the above line but can't get anything to work - it pulls back AccountHistory data and a proxy for the Trust; however it says no Trust row with given identifier.
This has to be something simple. Anyone?
Thanks in advance.
