Efficient complex NHibernate Criteria query

Question

I have an APVendor class, which has a collection of APInvoice classes and a unique Name; each APInvoice has a collection of APPayment classes. Each APPayment object references exactly one BankAccount class, a ClearedDate, an Amount, and a CheckNumber property.

With a bank statement that has a check record, let's say my Bank of Foo checking account, check 1111, for the amount of $1000.00, I want to see if the payment exists in my persisted layer, and mark the APPayment object's ClearedDate property.

I can do this by querying the vendor, then querying the vendor's invoices, then looking for that check number written against the BankAccount, but I'm sure there's a much more efficient way to write this as one Criteria query.

Can someone help me with how that should look? Here's how I would write the query in SQL to fetch the object graph I would need to work with:

select p.*, i.*, v.*
from appayments p
join bankaccounts a on p.bankaccountid = a.bankaccountid
join apinvoices i on i.invoiceid = p.invoiceid
join apvendors v on v.vendorid = i.vendorid
where a.bankaccountid = ????
and p.checknumber = ????

What exactly are you searching for? The APPayment which has a given CheckNumber and a given Amount? Any other restriction? Try to express your query in English, and the HQL query will follow. — JB Nizet, Jun 20 '12 at 13:55
@JBNizet, I expressed what I want in SQL... it seems easier to explain than english. Does it make sense? — Jeremy Holovacs, Jun 20 '12 at 17:27

score 1 · Accepted Answer · answered Jun 20 '12 at 17:35

The HQL query would be very similar to the SQL query:

select payment from APPayment payment
inner join payment.BankAccount bankAccount
left join fetch payment.Invoice invoice
left join fetch invoice.Vendor vendor
where bankAccount.id = :accountId
and payment.checkNumber = :checkNumber

The Criteria query is left as an exercise, but I don't see the poin tin using Criteria for such a static query.

Efficient complex NHibernate Criteria query

1 Answers1