NHibernate mapping: references with additional constraint

Question

Lets say I have this class

public class LinkingTable
{
    public int PrimaryKey { get; set; }
    public int LinkFk { get; set; }
    public string LinkTable { get; set; }

    public OtherTable OtherTable { get; set; }
}

and some other class

public class OtherTable
{
    public int PrimaryKey { get; set; }
    ... other properties
}

The mapping would look kind of like this

LinkingTableMap() : ClassMap<LinkingTable>
{
    Id(x => x.PrimaryKey);
    Map(x => x.LinkFK);
    Map(x => x.LinkTable);
    References(x => x.OtherTable, nameof(LinkingTable.LinkFk));
}

OtherTableMap() : ClassMap<OtherTable>
{
    Id(x => x.PrimaryKey);
    ... other mappings
}

I want to add a constraint to the OtherTable reference in LinkingTableMap. Something like

References(x => x.OtherTable, nameof(LinkingTable.LinkFk)).Where(x => x.LinkTable == "OtherTable");

The Where method obviously doesn't exist. I need to add this constraint because I might have a third table that may have duplicate primary keys. Is something like this possible?

small edit

The constraints are constants, I don't want to reference another column (which wouldn't exist)

Tip: Make sure all properties and methods on your model are `virtual`. — Felipe Oriani, Nov 12 '17 at 11:53
Will a filter work for you? https://weblogs.asp.net/ricardoperes/lesser-known-nhibernate-features-filters — David Osborne, Nov 13 '17 at 09:11
@FelipeOriani You're right. I forgot that for the purposes of this post — ZrSiO4, Nov 13 '17 at 18:11
@DavidOsborne That feels like its too broad. I'll explore it to see if I can make it work for my requirements. Basically I could have another property like OtherTable2 linked by LinkFk = 123 and LinkTable = 'OtherTable2'. Though not likely, a row in OtherTable and OtherTable2 might share the same primary key, so the differentiator is the LinkTable column. — ZrSiO4, Nov 13 '17 at 18:14

score 0 · Accepted Answer · answered Nov 22 '17 at 22:50

Ok, I've found a way to do it that seems to work alright.

When mapping the reference to the entity property, I add a formula to constrain the key property like this

References(x => x.OtherTable).Formula("(case when LinkTable = 'OtherTable' then LinkFk else 0 end)")

That results in sql that looks like this

select * 
from [LinkingTable] linkingTable
left outer join [OtherTable] otherTable on (case when linkingTable.LinkTable = 'OtherTable' then linkingTable.LinkFk else 0 end)=otherTable.PrimaryKey

NHibernate mapping: references with additional constraint

1 Answers1