One-to-one or many-to-one mapping using primary key of each table

Question

I've got a legacy schema with a main table and secondary table, where the secondary table connects to the main table by having the same primary key (a Secondary doesn't not necessarily exist for a given Main). I've been searching up-and-down for XML mappings that will make this work but haven't found anything that works for me.

  <class name="Secondary" table="Secondary" lazy="true" dynamic-insert="true" dynamic-update="true">
    <id name="mainId" type="Int32">
      <column name="MAIN_ID" not-null="true" />
      <generator class="foreign">
        <param name="property">Main</param>
      </generator>
    </id>
    <one-to-one class="Main" name="Main" constrained="true" />
  </class>


  <class name="Main" table="Main" lazy="true" dynamic-insert="true" dynamic-update="true">
    <one-to-one name="Secondary" cascade="all-delete-orphan" class="Secondary" />

Also tried on the Main side, still no go. It doesn't necessarily break, but it certainly doesn't do what I expect. For example:

session.Query<Main>().Count(m => m.Secondary != null) generates

select
    cast(count(*) as INT) as col_0_0_ 
from
    MAIN main0_ 
where
    main0_.MAIN_ID is not null

Note that it's using the MAIN_ID from the MAIN table and is ignoring Secondary altogether.

Answer 1

This issue is fixed in NHibernate 5.3

It's a known issue (PR with suggested fix is here).

For now as a workaround in LINQ you can call Count on some non-ID and not-nullable property:

session.Query<Main>().Count(m => m.Secondary.NotNullableProperty != null)

If such property doesn't exist (or you just want to easily find all such hacky usages in future) you can additionally map your Id column as read-only property and use it instead:

<property name="ForceJoinId" not-null="true" column="MAIN_ID" insert="false" update="false" />

session.Query<Main>().Count(m => m.Secondary.ForceJoinId != null)