StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs it possible to map multiple tables to a single domain model using NHibernate?
text
Body
copied!<p>Is it possible to map multiple tables to a single domain model using NHibernate? It's basically a <strong>UNION</strong>, something like shown below but I am not sure how to do the domain models in C# and mappings of NHibernate. </p> <p>I basically have <strong>1 abstract</strong> class and <strong>2 concrete</strong> classes. Each concrete class can be mapped to <strong>a single table</strong> in the databse.</p> <p>SQL statement:</p> <pre><code>SELECT * FROM InCompleOrders UNION SELECT * FROM CompleteOrders </code></pre> <p><strong>At the moment I am doing like this:</strong></p> <p>C# domain models:</p> <pre><code>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;} } </code></pre> <p>Database tables:</p> <pre><code>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 </code></pre> <p>NHibernate mapping:</p> <pre><code><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> </code></pre> <p><strong>I want to avoid doing things like:</strong></p> <pre><code>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; } </code></pre> <p><strong>And I want to be able to do things like:</strong></p> <pre><code>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; } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload