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

Question

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;
}

Answer 1

Have you considered using a [view][1]?

You can define your view as follows:

CREATE VIEW AllOrders AS
  SELECT CompleteOrderId
         , Status 
  FROM   CompleteOrders 
  WHERE  Reference = 'abc'

  UNION

  SELECT InCompleOrderId 
         , 1 AS 'Status'
  FROM   InCompleOrders 
  WHERE  Reference = 'abc'

From Hibernate's (and presumably NHibernate's) point of view, AllOrders is just another table to map your domain object to.

One caveat is that you will not be able to perform inserts to the view (updates may or may not work, I'm not sure). So if you need to perform write operations, you may still need to map to CompleteOrders and InCompleteOrders independently.

Edit: Looks like I was mistaken about not being able to insert data into views - http://www.google.com/search?q=insertable%20view

Answer 2

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!