Databases
StackOverflow2013
Postsdbo
PONhibernate/Hibernate, lookup tables and object design
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONhibernate/Hibernate, lookup tables and object design
    text
    copied!<p>I've got two tables. Invoice with columns CustomerID, InvoiceDate, Value, InvoiceTypeID (CustomerID and InvoiceDate make up a composite key) and InvoiceType with InvoiceTypeID and InvoiceTypeName columns.</p> <p>I know I can create my objects like:</p> <pre><code>public class Invoice { public virtual int CustomerID { get; set; } public virtual DateTime InvoiceDate { get; set; } public virtual decimal Value { get; set; } public virtual InvoiceType InvoiceType { get; set; } } public class InvoiceType { public virtual InvoiceTypeID { get; set; } public virtual InvoiceTypeName { get; set; } } </code></pre> <p>So the generated sql would look something like:</p> <pre><code>SELECT CustomerID, InvoiceDate, Value, InvoiceTypeID FROM Invoice WHERE CustomerID = x AND InvoiceDate = y SELECT InvoiceTypeID, InvoiceTypeName FROM InvoiceType WHERE InvoiceTypeID = z </code></pre> <p>But rather that having two select queries executed to retrieve the data I would rather have one. I would also like to avoid using child object for simple lookup lists. So my object would look something like:</p> <pre><code>public class Invoice { public virtual int CustomerID { get; set; } public virtual DateTime InvoiceDate { get; set; } public virtual decimal Value { get; set; } public virtual InvoiceTypeID { get; set; } public virtual InvoiceTypeName { get; set; } } </code></pre> <p>And my sql would look something like: </p> <pre><code>SELECT CustomerID, InvoiceDate, Value, InvoiceTypeID FROM Invoice INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.InvoiceTypeID WHERE CustomerID = x AND InvoiceDate = y </code></pre> <p>My question is how do I create the mapping for this?</p> <p>I've tried using join but this tried to join using CustomerID and InvoiceDate, am I missing something obvious?</p> <p>Thanks</p>
 

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