NHibernate Fluid - How to map the foreign key column as property


I am sure this is a straightforward question but consider the following: I have a reference between company and sector as follows:

public class Company {
    public Guid ID { get; set; }
    public Sector Sector { get; set; }
    public Guid SectorID { get; set; }

public class Sector {
    public Guid ID { get; set; }
    public string Name { get; set; }

Ok. What I want is the SectorID of the Company object to be populated after I go:

(new Company()).Sector = new Sector() { Name="asdf" }

and do a flush.

The mapping I am using kindly creates an additional column in the database called Sector_Id in the Company table, but this is not available as a property on Company. I want the SectorID property to be filled.

The mapping I'm currently using in the CompanyMap is

References(c => c.Sector).Cascade.All();

Does anyone have any ideas?

Thanks for your response. Sadly if I do the second option (set the column name of the column to be the same as the property, or set Map(x => x.SectorID, "Sector_Id") then I get the error:

System.IndexOutOfRangeException: Invalid index 7 for this SqlParameterCollection with Count=7.

I may have to do the first option but I am concerned that an additional query will be fired when you call the SectorID get as it gets the Sector itself out of the db (unless it is eager loaded which is a bit of a hassle).

I am surprised there is not an easy answer to this.

WOW! If I use

public virtual Guid SectorID
    get { return Sector.ID;

then nhibernate is clever enough to know that the Sector_id column in the Organisation query is actually the same thing as Sector.ID and it returns this under the hoods. It does not send off an additional query even if you lazy load. I am impressed!

As a follow up... It seems that hibernate is not really written to be able to map the foreign key column in the objects. Although this can be a bit of a pain in web front ends it makes sense as this is really a persistence concern not really an object concern. I am using asp.net MVC and have written a custom model binder that will take a input box of name Contact (rather than ContactID), new up a new Contact with the ID of what is in the texbox, and then apply this to the property of the Model. This gets around the issue with dropdown lists in web front ends. Will post code if anyone is interested.

This is easily done with a formula property.

public class Company {
  public virtual Guid Id { get; set; }
  public virtual Guid? SectorId { get; set; }
  public virtual Sector Sector { get; set; }

public class CompanyMap : ClassMap<Company> {
  public CompanyMap() {
    Id(x => x.Id); // Maps to a column named "Id"
    References(x => x.Sector); // Maps to a column named "Sector_id", unless you change the NHibernate default mapping rules.
    Map(x => x.SectorId).Formula("[Sector_id]");

This should act exactly how you want. When the Company is new, SectorId will be null; when Company is fetched from the DB, SectorId will be populated with the given formula value. The SectorId is exposed as a property, which makes it really nice for dealing with web drop downs, etc. When you save, you'll still need to bind the "real" association. Assuming that SectorId was selected in a form...

using (var txn = session.BeginTransaction()) {
  // Set the FK reference.
  company.Sector = session.Load<Sector>(company.SectorId);
  // Save the new record.
  // Commit the transaction.