Two many-to-one relationships referencing the same column NHibernate

Question

Have been looking around for the past half-day & have come up dry. Essentially, I'm working on a legacy app, the preexisting NHibernate configuration mapped a single property / column on the DB-end to two different "many-to-one" relations, and thus two different C# properties. The tables categories and types share the same primary keys / indexes, but there's no explicit relationship in MySQL or NHibernate.

Previously these tables have been "read-only" and it's worked in the application while avoiding exposing this as an issue. But, I've been recently tasked with allowing users to modify and persist changes to the grid table (has other properties I've not listed for brevity).

NHibernate threw a cryptic exception relating, I believe, to the fact that it's trying to save two separate "Domain" level objects to a single column reference. I looked through the general_log and the update query didn't reach the database. Commenting out the "Cat" line in the .hbm.xml config file resolved the "error", but how can I fix the mapping such that Cat and GridType need to have the same ID value and I can persist grid changes back to the DB?

public class Grid
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual GridType GridType { get; set; }
    public virtual Cat Cat { get; set; }
    ...
}

The DB schema looks like this (maintaining existing weird setup):

CREATE TABLE `categories` 
  `CatID` smallint(4) NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
  ...
  PRIMARY KEY (`CatID`)

CREATE TABLE `types`
  `TypeID` smallint(4) NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
  ...
  PRIMARY KEY (`TypeID`)

CREATE TABLE `grid` 
  `GridID` smallint(6) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `TypeID` smallint(4) NOT NULL,
  ...
  PRIMARY KEY (`GridID`),
  KEY `GridTypeId` (`TypeID`),
  CONSTRAINT `GridTypeId` FOREIGN KEY (`TypeID`) REFERENCES `types` (`TypeID`),

The table grid references the table types via a foreign key in MySQL, but nothing on the DB-end ties it to categories. The NHibernate configuration is currently set up like this with the only exception being previously the "grid" configuration was previously mutable="false" and cache usage="read-only":

  <class name="Grid, Domain" table="grid" lazy="true">
    <cache usage="read-write" />
    <id name="Id" column="GridID" unsaved-value="0">
      <generator class="identity" />
    </id>
    ...
    <many-to-one name="GridType" column="TypeID" fetch="join" not-null="true" />
    <many-to-one name="Cat" column="TypeID" not-null="true" />
  </class>

  <class name="Cat, Domain" table="categories" lazy="true" mutable="false">
    <cache usage="read-only"/>
    <id name="Id" column="CatID" unsaved-value="0">
      <generator class="identity" />
    </id>
    <property name="Name" />
    ...
  </class>

  <class name="GridType, Domain" table="types" mutable="false">
    <cache usage="read-only"/>
    <id name="Id" column="TypeID"/>
    <property name="Name" />
    ...
  </class>

Will try and convince my boss that this is worth restructuring, because this whole area of the DB is a mess, but this is a legacy application; so I want to know what my options are depending on what our availability for future development looks like.

---

Edit: Trialing what Radim Köhler suggested below. Only hiccup was that (a) Someone could switch the value of Cat and errantly believe they'd made a change to the DB, but it won't be persisted. And (b) if someone updated GridType, it'd update the DB but not the cache; Cat would remain the old value until NHibernate pulled back from the DB again (i.e. IDs of Cat and GridType would be "de-synced" which was part of my question).

Fix I'm trialing is "when the GridType / Cat values on Grid are set, set the other one by fetching the appropriate ID from cache". Will also prevent someone from setting a gridtype / cat that may not exist in the other table.

public class Grid {

    ...

    private GridType _gridType;
    private Cat _cat;

    public virtual GridType GridType
    {
        get => _gridType;
        set
        {
            var cat = _catRepository.GetById(value.Id);
            _cat = cat ?? throw new InvalidOperationException("GridType is not valid Cat");
            _gridType = value;
        }
    }

And repeat the above for public virtual Cat Cat. If anyone sees an issue with this, please feel free to leave a comment.

Answer 1

We can use one column in mapping for multiple purposes... but only one property can be editable ... all other must be readonly. We can use insert="false" update="false"

This would work in our case:

<many-to-one name="GridType" column="TypeID" ... />
<many-to-one name="Cat"      column="TypeID" insert="false" update="false" />