ICriteria complex query

letmein2 · **Joined:** Fri Feb 09, 2007 3:47 pm **Posts:** 56

Hi guys,

I'm having a hard time trying to express a query using the ICriteria interface.

A little background: I have Bills and each of them can have a Payment. Each Bill has an amount of money to be payed and each Payment has the amount payed. So far so good.

The sum of all the Payment' amounts for a Bill can't be greater than the Bill's amount. If the sum is equal to the Bill's amount, then that Bill is payed.

Enter the search GUI.
With it I can search my Bills based on year, type and so on, if the user
specify the parameter:

[...]
ICriteria criteria = session.CreateCriteria (typeof (Bill));

if (textboxYear.Text != "") {
criteria.add ("year", textboxYear.Text)
}

if (JustPayedBills) {
???
}

[...]

Once the criteria is constructed with my parameters, it gets passed to a worker that request the results and updates the UI.
My problem is that I can't find a way to *add* the payed/unpayed bill restriction to my existing criteria.

TIA.

mnichols · **Joined:** Mon Aug 21, 2006 9:18 am **Posts:** 179

I am not completely sure what you are trying to return from your DB, but if you want to get the Payment amounts compared to the Bill amount, then you could use Projections and DetachedCriteria and Subqueries to first create your SUM projections on the Payment.Amount, then create a DetachedCriteria subquery for Payment to apply those projections. Finally, you have your DetachedCriteria for your Bill do an .Add(Subqueries...) and stick your Payment criteria in there. That would create a WHERE clause that would reeturn the Bills which have PaymentAmounts whose sums equal the Bill amount.

This is all predicated upon you using v 1.2 tho...I hope that is case :)
MIKE

letmein2 · **Joined:** Fri Feb 09, 2007 3:47 pm **Posts:** 56

Hi Mike, thanks for your response!

I want to get all the Bills matching my search parameters. I'm using NHibernate 1.2 (beta 3).

I'm kinda lost following your suggestions, here is the first step:

DetachedCriteria d = DetachedCriteria.For (typeof (Payment));
d.SetProjection (Projections.ProjectionList ()
.Add (Projections.Sum ("Amount")));

Do I need a subquery here?

And what do you mean with "then create a DetachedCriteria subquery for Payment to apply those projections"?

Thanks again!

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

You can add criteria for associated entities by creating a sub-criteria with ICriteria subcriteria = criteria.CreateCriteria(associationPropertyName) - this is sort of like adding a join in HQL.

You can then add criterion against the newly created subcriteria like you would against the main criteria.

Symon.

letmein2 · **Joined:** Fri Feb 09, 2007 3:47 pm **Posts:** 56

Thanks Symon, I did know this, but I can't manage to pull out something useful.

I've tried this:

Code:

DetachedCriteria d1 = DetachedCriteria.For (typeof (Payment));
DetachedCriteria d2 = DetachedCriteria.For (typeof (Bill));
                               
d1.SetProjection (Projections.ProjectionList ()
                         .Add (Projections.Sum ("Amount"))
                         .Add (Projections.GroupProperty ("Bill")));

/* Note that "Payments" is a IList attribute in Bill */
d2.CreateCriteria ("Payments").Add (Subqueries.Exists (d1));

MyMainCriteria.Add (Subqueries.PropertyEq ("Amount", d2));

That results in a "Object reference not set to an instance of an object."

:-(

fabiob · **Joined:** Sat Dec 16, 2006 11:10 am **Posts:** 2

I think you need something like:

Code:

// set an alias for the root Bill
ICriteria mainCriteria = session.CreateCriteria(typeof(Bill), "bill"); 

DetachedCriteria d1 = DetachedCriteria.For(typeof (Payment))
   // only payments of the bill
  .Add(Property.ForName("Bill").EqProperty("bill.id"))
  // return the sum of the amount
  .SetProjection(Projections.Sum("Amount"));

mainCriteria.Add(Subqueries.PropertyEq("Amount", d1));