Nhibernate one-to-many with table per subclass
- by Wayne
I am customizing N2CMS's database structure, and met with an issue. The two classes are listed below.
public class Customer : ContentItem
{
public IList<License> Licenses { get; set; }
}
public class License : ContentItem
{
public Customer Customer { get; set; }
}
The nhibernate mapping are as follows.
<class name="N2.ContentItem,N2" table="n2item">
<cache usage="read-write" />
<id name="ID" column="ID" type="Int32" unsaved-value="0" access="property">
<generator class="native" />
</id>
<discriminator column="Type" type="String" />
</class>
<subclass name="My.Customer,My" extends="N2.ContentItem,N2" discriminator-value="Customer">
<join table="Customer">
<key column="ItemID" />
<bag name="Licenses" generic="true" inverse="true">
<key column="CustomerID" />
<one-to-many class="My.License,My"/>
</bag>
</join>
</subclass>
<subclass name="My.License,My" extends="N2.ContentItem,N2" discriminator-value="License">
<join table="License" fetch="select">
<key column="ItemID" />
<many-to-one name="Customer" column="CustomerID" class="My.Customer,My" not-null="false" />
</join>
</subclass>
Then, when get an instance of Customer, the customer.Licenses is always empty, but actually there are licenses in the database for the customer. When I check the nhibernate log file, I find that the SQL query is like:
SELECT licenses0_.CustomerID as CustomerID1_,
licenses0_.ID as ID1_,
licenses0_.ID as ID2_0_,
licenses0_1_.CustomerID as CustomerID7_0_,
FROM n2item licenses0_
inner join License licenses0_1_
on licenses0_.ID = licenses0_1_.ItemID
WHERE licenses0_.CustomerID = 12 /* @p0 */
It seems that nhibernate believes that the CustomerID is in the 'n2item' table. I don't know why, but to make it work, I think the SQL should be something like this.
SELECT licenses0_.ID as ID1_,
licenses0_.ID as ID2_0_,
licenses0_1_.CustomerID as CustomerID7_0_,
FROM n2item licenses0_
inner join License licenses0_1_
on licenses0_.ID = licenses0_1_.ItemID
WHERE licenses0_1_.CustomerID = 12 /* @p0 */
Could any one point out what's wrong with my mappings? And how can I get the correct licenses of one customer? Thanks in advance.
