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

mappingnhibernateunion

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

Best Answer

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!

Related Topic