nhibernatefluent-nhibernate-mapping

NHibernate mapping: references with additional constraint


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)


Solution

  • 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