How to construct this NHibernate query

Question

I have a one-to-many relationship. I would like to construct this query:

Give me all the parents that have only one child and for this child child.Type=X

Since I 'm learning, please show me the query with the Criteria API and with HQL.

And btw, is there any automatic way to know what HQL is identical to a criteria expression ?

Update:

It seems I found how to do it in HQL:

@"select Parent
    from Parent parent
        join parent.Children ch
        where (ch.Type = :chType) and
              (select count(*) from parent.Children) = 1")

But is it well done? How is the performance? I have the intuition that the count(*) is not well placed...

I think you will get a better response if you post your mapping file, code class, and some sample data. — David P
– David P, Commented Jan 23, 2009 at 19:22

joshua.ewer · Accepted Answer · 2009-01-23 22:10:02Z

1

Agreed with @David Pellerin about the ambiguity of the question. However, I was literally just putting together a Lunch'n'Learn about this very topic, so using the typical Customer, Orders relationship . . .

Criteria API:

public IList<Customer> GetCustomersWithOrdersAfterDate(DateTime sinceDate)
{
   return Session.ICriteria.CreateCriteria(typeof(Customer))
      .CreateCriteria("Orders")
      .Add(Expression.Gt("OrderDate", sinceDate))
      .List<Customer>();
}

HQL (which looks a ton like SQL, only you don't have to know the entire relationship graph):

public IList<Customer> GetCustomersWithOrdersAfterDate(DateTime sinceDate)
{
    Session.CreateQuery("select c from Customer c, c.Orders.elements o WHERE o.OrderDate > :orderDate).SetDateTime("orderDate", sinceDate).List<Customer>();
}

BTW:
if you are just learning, stop everything you are doing and go watch these screencasts. Probably the best set of videos I've seen regarding nHibernate. Highly, highly recommended.

Another useful link to address your "having count = 1" requirement is here. Fairly complicated, but I've adapted something similar with some success.

edited Jan 23, 2009 at 22:10

answered Jan 23, 2009 at 20:10

joshua.ewer

3,9823 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicolas Cadilhac Over a year ago

Thanks. My question was theoretical, that's why I have no example. I know the basic of the criteria api as you are showing it, but you don't tell me the main thing I was requesting: in your example, I want to get the customers who have only one order.

Collectives™ on Stack Overflow

How to construct this NHibernate query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related