CODEWITHSUNDEEP
nhibernatenhibernate-mapping
Marc Chu
Marc Chu

Reputation: 311

NHibernate: Mapping a one-to-many relation in a SQL View

I'm working with a SQL view, and I don't know enough about NHibernate, views, or databases in general, to know where my problem lies. The view I'm working with used to have a string column, "ExemptionCode". Now, the view can potentially have many exemption codes. Here is the XML mapping for the new relation:

<class name="LatestDocumentVersion" table="LatestDocumentVersion" mutable="false" schema-action="none">
    <id name="DocumentVersionID" type="Int32"/>        
    <property name="ContainerDocumentID" type="Int32"/>
    <!--<property name="ExemptionCode" length="10" />-->
    <set name="ExemptionCodes" cascade="all-delete-orphan" lazy="false" inverse="false">
      <key column="ContainerDocumentID"/>
      <one-to-many class="ContainerDocumentExemptions"/>
    </set>
    <--Properties omitted-->
</class>

Here is the mapping for the ContainerDocumentExemptions class:

<class name ="ContainerDocumentExemptions" lazy ="false">
  <id name ="ContainerDocumentExemptionID" type="Int32">
    <generator class="identity"/>
  </id>
  <many-to-one name="ContainerDocumentID" class="ContainerDocuments" column="ContainerDocumentID" cascade="none"/>
  <property name="ExemptionCode" length="10"/>
</class>

The ContainerDocumentExemption class actually has a bi-directional one-to-many relationship with a ContainerDocument object. Here's the other end:

<class name="ContainerDocuments" lazy="false" table="ContainerDocuments">
    <id name="ContainerDocumentID" type="Int32">
        <generator class="identity"/>
    </id>
    <!--<property name="ExemptionCode" length="10" />-->
    <set name="Exemptions" cascade="all-delete-orphan" lazy="false" inverse="true">
      <key column="ContainerDocumentID"/>
      <one-to-many class="ContainerDocumentExemptions"/>
    </set>
    <--Properties omitted-->
</class>

After adding this line to the ContainerDocuments class, ContainerDocuments can correctly write to and read from the new ContainerDocumentExemptions table: 

public class ContainerDocuments {
    public virtual ISet<ContainerDocumentExemptions> Exemptions { get; set; }
    //Properties omitted
}

So, I added this code to the LatestDocumentVersion class:

public class LatestDocumentVersion {
    public virtual int ContainerDocumentID { get; set; }
    public virtual ISet<ContainerDocumentExemption ExemptionCodes { get; set; }
    //properties omitted
}

LatestDocumentVersion is a view that performs inner joins and outer joins on a bunch of different tables, and takes a bunch of different columns from each. (The SQL that creates the view is very complicated, and it's hopefully irrelevant to the problem at hand.) The newly added LatestDocumentVersion.ContainerDocumentID, which is a foreign key into the ContainerDocumentExemptions table, always populates correctly. However, the ExemptionCodes collection always remains empty.

I have a feeling that part of the problem is the ContainerDocument back-reference in the ContainerDocumentExemptions class. Could this prevent me from using the same mapping in the LatestDocumentVersion class? I thought that making the LatestDocumentVersion-ContainerDocumentExemptions relation uni-directional would mitigate that issue, if it were a problem. So how can I populate the LatestDocumentVersion.ExemptionCodes field?? Could anyone at least give me hints as to how to debug the problem?

Upvotes: 0

Views: 660

Answers (1)

Firo
Firo

Reputation: 30813

ContainerDocumentID is the id on the ContainerDocument class but it is not on the LatestDocumentVersion class and by default each one-to-many joins back on the id. Add property-ref to the LatestDocumentVersion mapping

<class name="LatestDocumentVersion" table="LatestDocumentVersion" mutable="false" schema-action="none">
    <id name="DocumentVersionID" />
    <property name="ContainerDocumentID"/>
    <set name="ExemptionCodes" cascade="all-delete-orphan" lazy="false" inverse="false">
      <key column="ContainerDocumentID" property-ref="ContainerDocumentID"/>
      <one-to-many class="ContainerDocumentExemptions"/>
    </set>
</class>

Upvotes: 1

Related Questions