Is it possible to map multiple tables to a single domain model using NHibernate?

mapping, nhibernate, union

Solution

Yes, there are a number of options you can use to do what you want. Ayende Rahien has a great demonstration of the options for table inheritance and the resulting table structures.

Using the class hierarchy that has an abstract BaseOrder as a parent to CompleteOrder and IncompleteOrder, the union case is:

<class name="CompleteOrder" table="CompleteOrders">
    <id name="Id">
        <generator class="identity"/>
    </id>
    <property name="Status"/>
</class>

<class name="IncompleteOrder" table="IncompleteOrders">
    <id name="Id">
        <generator class="identity"/>
    </id>
</class>

or

 <class name="BaseOrder" abstract="true" table="Orders">
    <id name="Id">
        <generator class="hilo"/>
    </id>

    <union-subclass table="CompleteOrders" name="CompleteOrder">
        <property name="Status"/>
    </union-subclass>

    <union-subclass table="IncompleteOrders" name="IncompleteOrder">
    </union-subclass>
</class>

which also has separate tables but doesn't use a union for the query.

Check out the article, it will probably help greatly.

edit:

There was a comment about not wanting to cast but it's now gone. I'm not sure if it was deleted or if it was a SO issue...

If BaseOrder.Status is a virtual property, you should be able to pass around a BaseOrder and not need to cast. You'll have to maintain the extra class hierarchy but you can still query them together and, for the most part, the rest of the application shouldn't need to know about the subclasses.

Good luck!

Problem

Is it possible to map multiple tables to a single domain model using NHibernate? It's basically a UNION, something like shown below but I am not sure how to do the domain models in C# and mappings of NHibernate. I basically have 1 abstract class and 2 concrete classes. Each concrete class can be mapped to a single table in the databse. SQL statement: ``` SELECT * FROM InCompleOrders UNION SELECT * FROM CompleteOrders ``` At the moment I am doing like this: C# domain models: ``` public enum Status { InComplete = 1, Pending = 2, Complete = 3 } public abstract class BaseOrder : Entity { public string Property1 {get;set;} public string Property2 {get;set;} public string Property3 {get;set;} public Status Status {get;set;} public string Reference {get;set;} //this is unique } public class InCompleteOrder : BaseOrder { public override Status Status { get { return Status.InComplete; } } } public class Order : BaseOrder { public DateTime DeliveredOn {get;set;} public DateTime PaidOn {get;set;} } ``` Database tables: ``` InCompleOrders table InCompleOrderId INT PK Property1 varchar(255) NULL Property2 varchar(255) NULL Property3 varchar(255) NULL CompleteOrders table CompleteOrderId INT PK Status INT Property1 varchar(255) NOT NULL Property2 varchar(255) NOT NULL Property3 varchar(255) NOT NULL DeliveredOn datetime NOT NULL PaidOn datetime NOT NULL ``` NHibernate mapping: ``` <class name="Order" table="CompleteOrders"> <id name="Id" column="CompleteOrderId" type="int"> <generator class ="hilo"></generator> </id> <property name="DeliveredOn" column="DeliveredOn" not-null="true" type="DateTime" /> <property name="PaidOn" column="PaidOn" not-null="true" type="DateTime" /> <property name="Property1" column="Property1" not-null="true" type="string" /> <property name="Property2" column="Property2" not-null="true" type="string" /> <property name="Property3" column="Property3" not-null="true" type="string" /> </class> <class name="InCompleteOrder " table="InCompleOrders"> <id name="Id" column="InCompleOrderId" type="int"> <generator class ="hilo"></generator> </id> <property name="Property1" column="Property1" not-null="false" type="string" /> <property name="Property2" column="Property2" not-null="false" type="string" /> <property name="Property3" column="Property3" not-null="false" type="string" /> </class> ``` I want to avoid doing things like: ``` public BaseOrder GetByReference (string reference) { BaseOrder bo; var repoOrder = new Repository<Order>(); bo = repoOrder.FindOne(query); //query = Restrictions.Eq("Reference", reference) if (bo == null) { var repoInCompOrder = new Repository<InCompleteOrder>(); bo = repoInCompOrder.FindOne(query); //query = Restrictions.Eq("Reference", reference) } return bo; } ``` And I want to be able to do things like: ``` public Order GetByReference (string reference) { var repoOrder = new Repository<Order>(); var bo = repoOrder.FindOne(query); //query = Restrictions.Eq("Reference", reference) //reference = "abc" //and this will generate a SQL similar to: // //SELECT CompleteOrderId // , Status //FROM CompleteOrders //WHERE Reference = 'abc' // //UNION // //SELECT InCompleOrderId // , 1 AS 'Status' //FROM InCompleOrders //WHERE Reference = 'abc' return bo; } ```

Original source