Map to view on custom field name - one-to-one relationship

Question

I am unable to get NHibernate to map the AccountCode column of the Beneficiary table AccountCode column in this one-to-one relationship (each Account has a single Beneficiary, each Beneficiary has a single Account).

Classes:

public class Account
{
    ...
    public virtual string Name { get; protected set; }
    public virtual string Code { get; protected set; }
}

public class Beneficiary
{
    ...
    public virtual int Id { get; set; }
    public virtual string Name { get; protected set; }
    public virtual Account Account { get; protected set; }
    public virtual BeneficiaryGroup Group { get; protected set; }
}

SQL:

CREATE VIEW dbo.Account AS
    SELECT DISTINCT RTRIM(LTRIM(ACCNT_NAME)) AS Name, 
                    RTRIM(LTRIM(ACCNT_CODE)) AS Code
    FROM myremoteserver.schema.tablename
    WHERE ACCNT_TYPE NOT IN ('B', 'P')


CREATE TABLE dbo.Beneficiary
(
    Id INT IDENTITY(1,1) NOT NULL, 
    BeneficiaryGroupId INT NOT NULL CONSTRAINT FK_Beneficiaries_BeneficiaryGroup FOREIGN KEY REFERENCES dbo.BeneficiaryGroup (Id), 
    Name VARCHAR(100) NOT NULL,
    AccountCode VARCHAR(100) NOT NULL,
    CONSTRAINT PK_Beneficiary PRIMARY KEY (Id)
)

When trying to use HasMany and different variants, NHibernate tries to join on the Beneficiary.Id column.

I had tried different variations of Map, References, Join (which tells me that the join already exists) and HasMany (which fails, as the relationship is indeed one-to-one).

How can I get NHibernate to map these two classes correctly to their columns?

---

When trying the different fluent mappings, in my IAutoMappingOverride<Beneficiary>, the following happens:

mapping.HasOne(b => b.Account);
mapping.HasOne(b => b.Account).PropertyRef(sa => sa.Code);
mapping.HasOne(b => b.Account).PropertyRef(sa => sa.Code).ForeignKey("none");

The generated SQL uses the Beneficiary.Id field instead of the Beneficiary.AccountCode. (before you ask, I am using "none" since Account is a view, it can't have a key).

mapping.Join("AccountCode", x => x.References(y => y.Account));
mapping.Join("Account", b => b.KeyColumn("AccountCode"));

Result in Tried to add join to table 'Account' when already added..

And:

mapping.Map(b => b.Account, "AccountCode");
mapping.Map(b => b.Account).ReadOnly().Column("AccountCode");

Result in:

Could not determine type for: .Account, , Version=1.0.0.0, Culture=neutral, PublicKeyToken=null, for columns: NHibernate.Mapping.Column(AccountCode)

mapping.References(b => b.Account).Column("Code");

Results in Invalid column name 'Code'..

And:

mapping.References(b => b.Account).Column("AccountCode");
mapping.References(b => b.Account).Column("AccountCode").Access.Property();

Overrides all my IReferenceConvention overrides (mapping some classes to have a <class name>Code key column).

When trying HasMany:

mapping.HasMany<Account>(b => b.Account).KeyColumn("AccountCode");

Custom type does not implement UserCollectionType: .Account

Answer 1

// in BeneficiaryMapping
mapping.References(b => b.Account)
    .Column("AccountCode" /* of Beneficiary table*/)
    .PropertyRef(a => a.Code); // use the Column of Code as the joincolumn in Account table

Answer 2

This approach is similar to Firo's but in a simpler mapping. It uses References like he suggest because that's how NH allows you select a property other than the entity primary key property to reference another type. The PropertyRef is not necessary since NH "knows" its going to use the AccountCode value as the the "key" into the Account view.

Here is how to map Account:

public class AccountMap : ClassMap<Account>
{
    public AccountMap()
    {
        // Code as a psuedo primary key in the view:
        Id(acc => acc.Code)
            .GeneratedBy.Assigned();

        Map(acc => acc.Name);

        // Add other mappings here...

        // Ensure NH doesn't try to update the lookup view:
        ReadOnly();
    }
}

Here is how Beneficiary looks mapped:

public class BeneficiaryMap : ClassMap<Beneficiary>
{
    public BeneficiaryMap()
    {
        Id(b => b.Id)
            .GeneratedBy.Identity()
            .UnsavedValue(0);

        Map(b => b.Name);
        // Other mapped properties...

        References<BeneficiaryGroup>(b => b.Group, "BeneficiaryGroupId");
        References<Account>(b => b.Account, "AccountCode");
    }
}