Nhibernate/Hibernate, lookup tables and object design

Question

Hi,

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.

I know I can create my objects like:

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

So the generated sql would look something like:

SELECT CustomerID, InvoiceDate, Value, InvoiceTypeID FROM Invoice WHERE CustomerID = x AND InvoiceDate = y
SELECT InvoiceTypeID, InvoiceTypeName FROM InvoiceType WHERE InvoiceTypeID = z

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:

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

And my sql would look something like:

SELECT CustomerID, InvoiceDate, Value, InvoiceTypeID 
FROM Invoice INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.InvoiceTypeID
WHERE CustomerID = x AND InvoiceDate = y

My question is how do I create the mapping for this?

I've tried using join but this tried to join using CustomerID and InvoiceDate, am I missing something obvious?

Thanks

Answer 1

If your goal is (as you said) to avoid two queries, you can retrieve the data using a single HQL statement:

select i, it from Invoice i fetch join i.type where ...

...as documented in the hibernate docs. This should execute only one SQL select statement and retrieve everything without any mapping changes.

This is a regular HQL query and is executed as follows:

IQuery q = s.CreateQuery("select i, it from Invoice i fetch join i.type where ...");
IList invoices = q.List();

More information is available on the hibernate query language page.