Join to a Table using two non-FK columns with Fluent NHibernate

Question

I'm using Fluent NHibernate and I have two tables:

 BusinessPlan [Id, Year, CustomerCode]

 PreviousYearData [Id, Year, CustomerCode, MoreFieldsForData]

In my domain, I want to join PreviousYearData to the BusinessPlan to make entities something like this:

public class BusinessPlan {
    public Guid Id { get; set; }
    public int Year { get; set; }
    public string CustomerCode { get; set; }
    public PreviousYearData PreviousYearData {get; set;}
}

public class PreviousYearData {
    public Guid Id { get; set; }
    public int Year { get; set; }
    public string CustomerCode { get; set; }
    // many more fields
}

The data in the PreviousYearData table gets prepopulated at the beginning of the year before the BusinessPlans will have been created, so I won't know what the BusinessPlan's Id will be and can't create a normal foreign key. What I think I want to do is join the PreviousYearData to BusinessPlan based on the two columns Year and CustomerCode. Is this possible with Fluent NHibernate? Is there another way to approach this that makes more sense?

Answer 1

I guess this or similar should work for you:

        HasMany(x => x.PreviousYearDatas )
            .Access.AsCamelCaseField(Prefix.Underscore)
            .WithKeyColumns("YEAR", "CUSTOMER_CODE")
            .LazyLoad();

You will have collection, but you will be able to get what you want.

Also there is another construction:

        HasMany( 
            // some mapping but includes one foreign key - say customer code
            .Where( "YEAR = 2010" )

Probably that is the best choice. I do not think that years are changing often :)

Answer 2

I don't see why there is a problem with having a foreign key in BusinessPlan table: PreviousYearDataId

in BusinessPlan mapping just add:

References(x => x.PreviousYearData )
  .Column("PreviousYearDataId")