NHibernate TPH策略多子类映射同来源集合报QuestionId重复错误

使用NHibernate进行ORM映射时，采用table-per-hierarchy（TPH，单表继承）策略存储抽象Question类，其两个子类SingleAnswerQuestion、MultiAnswerOptionQuestion均包含指向独立数据表QuestionAnswerOptions的集合类型属性AnswerOptions。在.hbm映射文件中配置该集合映射时，NHibernate抛出如下错误：
The column 'QuestionId' has already been added in this SQL builder.
经测试，仅保留任意一个子类的映射配置时，映射可正常运行（仅对当前保留的子类生效），初步判断为重复映射问题，需要定位配置遗漏点并给出可生效的调整方案。

涉及的原始Question类映射配置如下：

<class name="Question" table="Questions" lazy="false">
  <id name="Id" type="Int64" column="Id">
    <generator class="seqhilo">
      <param name="sequence">CommonId</param>
    </generator>
  </id>
  <discriminator column="QuestionType" type="String" insert="false"/>

  <natural-id>
    <component name="QuestionId" class="QuestionId">
      <property name="Id" column="QuestionId" not-null="true" />
    </component>
  </natural-id>

  ....

  <subclass name="SingleAnswerQuestion" discriminator-value="qtid_SingleAnswer">
    <set name = "AnswerOptions" table = "QuestionAnswerOptions" cascade="all-delete-orphan"  lazy="false">
      <key column = "QuestionId"  not-null="true" foreign-key="IX-QuestionId"/>
      <one-to-many class = "Questions.QuestionAnswerOption"/>
    </set>
      ...
  </subclass>

  <subclass name="MultiAnswerOptionQuestion" discriminator-value="qtid_MultiAnswer">
    <set name = "AnswerOptions" table = "QuestionAnswerOptions" cascade="all-delete-orphan"  lazy="false">
      <key column = "QuestionId"  not-null="true" foreign-key="IX-QuestionId"/>
      <one-to-many class = "QuestionAnswerOption" />
    </set>
      ...
  </subclass>
</class>

集合元素QuestionAnswerOption的原始映射配置如下：

<class name="QuestionAnswerOption" table="QuestionAnswerOptions" lazy="false">
  <id name="Id" type="Int64" column="Id">
    <generator class="seqhilo">
      <param name="sequence">CommonId</param>
    </generator>
  </id>

  <property name="AttachmentId" column="AttachmentId" not-null="false"/>
  <property name="ThumbnailAttachmentId" column="ThumbnailAttachmentId" not-null="false"/>
  <property name="Description" column="Description" not-null="true"/>
  <property name="OrderNumber" column="OrderNumber" not-null="true"/>
</class>

• 核心配置冲突：TPH映射模式下，NHibernate会将所有子类的关联映射纳入基类的全局SQL构建逻辑。你在两个子类中分别配置了指向同一张QuestionAnswerOptions表、且外键列同为QuestionId的集合，框架在生成关联查询/写入语句时会两次尝试向SQL构造器添加同名外键列，直接触发重复列报错。
• 基础映射缺失：QuestionAnswerOptions表的映射文件中，完全没有定义关联Question表的外键字段QuestionId，也没有配置多对一反向关联，映射本身不完整。
• 业务逻辑缺陷：两个子类的集合共用同一张答案选项表，却没有配置数据过滤规则，NHibernate无法区分某条选项数据归属单选还是多选题类，即使绕过报错也会出现数据查询交叉的问题。

根据业务场景选择对应方案即可：

方案1（推荐，适用90%以上场景）：两个子类的AnswerOptions逻辑完全一致 #

如果单选、多选题的答案选项业务逻辑、字段结构、级联规则完全一致，直接将集合映射上移到基类Question层级，从根源避免重复定义，同时补全选项表的外键映射：

1. 修改Question类映射，将AnswerOptions的set块从两个子类中删除，移到基类配置中，建议添加inverse="true"由多端维护外键，提升性能：

<class name="Question" table="Questions" lazy="false">
  <!-- 原有基类id、discriminator、natural-id等配置保留不变 -->
  <id name="Id" type="Int64" column="Id">
    <generator class="seqhilo">
      <param name="sequence">CommonId</param>
    </generator>
  </id>
  <discriminator column="QuestionType" type="String" insert="false"/>
  <natural-id>
    <component name="QuestionId" class="QuestionId">
      <property name="Id" column="QuestionId" not-null="true" />
    </component>
  </natural-id>

  <!-- 共有的集合映射移到基类层级 -->
  <set name = "AnswerOptions" table = "QuestionAnswerOptions" cascade="all-delete-orphan"  lazy="false" inverse="true">
    <key column = "QuestionId"  not-null="true" foreign-key="IX-QuestionId"/>
    <one-to-many class = "Questions.QuestionAnswerOption"/>
  </set>

  <subclass name="SingleAnswerQuestion" discriminator-value="qtid_SingleAnswer">
    <!-- 仅保留子类独有字段配置，删除原有AnswerOptions的set块 -->
  </subclass>
  <subclass name="MultiAnswerOptionQuestion" discriminator-value="qtid_MultiAnswer">
    <!-- 仅保留子类独有字段配置，删除原有AnswerOptions的set块 -->
  </subclass>
</class>

2. 补全QuestionAnswerOption的映射，添加外键关联：

<class name="QuestionAnswerOption" table="QuestionAnswerOptions" lazy="false">
  <id name="Id" type="Int64" column="Id">
    <generator class="seqhilo">
      <param name="sequence">CommonId</param>
    </generator>
  </id>
  <!-- 补全缺失的外键多对一映射 -->
  <many-to-one name="Question" column="QuestionId" not-null="true" class="Question"/>
  <property name="AttachmentId" column="AttachmentId" not-null="false"/>
  <property name="ThumbnailAttachmentId" column="ThumbnailAttachmentId" not-null="false"/>
  <property name="Description" column="Description" not-null="true"/>
  <property name="OrderNumber" column="OrderNumber" not-null="true"/>
</class>

方案2：两个子类的AnswerOptions需要独立配置、业务逻辑存在差异 #

如果确实需要在子类层级分别配置集合（比如后续两个子类的选项会有差异化字段、级联规则不同），需要做两个调整：

• 给集合的key节点添加property-ref配置，关联到基类中定义的QuestionId自然键而非主键Id，避免NHibernate重复生成同名列定义
• 给QuestionAnswerOptions表添加QuestionType鉴别字段，在集合映射中增加where条件过滤当前子类对应的选项数据，避免不同子类的集合数据交叉

子类集合配置示例：

<subclass name="SingleAnswerQuestion" discriminator-value="qtid_SingleAnswer">
  <set name = "AnswerOptions" table = "QuestionAnswerOptions" cascade="all-delete-orphan" lazy="false" where="QuestionType='qtid_SingleAnswer'">
    <key column = "QuestionId" not-null="true" foreign-key="IX-QuestionId" property-ref="QuestionId.Id"/>
    <one-to-many class = "Questions.QuestionAnswerOption"/>
  </set>
</subclass>
<subclass name="MultiAnswerOptionQuestion" discriminator-value="qtid_MultiAnswer">
  <set name = "AnswerOptions" table = "QuestionAnswerOptions" cascade="all-delete-orphan" lazy="false" where="QuestionType='qtid_MultiAnswer'">
    <key column = "QuestionId" not-null="true" foreign-key="IX-QuestionId" property-ref="QuestionId.Id"/>
    <one-to-many class = "QuestionAnswerOption" />
  </set>
</subclass>

注意：该方案下必须在QuestionAnswerOptions表新增QuestionType字段，存储对应题目的类型鉴别值，同时在QuestionAnswerOption的映射中添加该字段的属性映射，否则where条件无法生效，查询时会拉取到归属错误的选项数据。

内容的提问来源于stack exchange，提问作者Link