Noah
Noah

Reputation: 15330

How can I include a nHibernate Criterion against a count of matching records

BACKGROUND:

I am looking for a nHibernate query that returns a list of all orders M that only contain parts with a specific size.

In other words, return all order containing only parts of size S, and exclude all orders that contain a mixture of sizes.

I was using:

matching_orders.Add(
 Expression.Conjunction()
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).In(DetailQueryOver(S)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(M)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(L)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(XL)))

There must be a better way. Something like "where count(DISTINCT SIZES) = 1"

But I'm not sure how to implement this in nHibernate.

Suggestions?

Upvotes: 0

Views: 199

Answers (1)

Daniel Schilling
Daniel Schilling

Reputation: 4977

As dotjoe suggested, I believe having clauses are accomplished by stashing a projection in a temporary variable, then using it in both the projection list and the restriction list, though I've only done that with ICriteria queries, not QueryOver.

Another way to write this query is to use two subqueries - one to represent the size you are looking for, another to represent all the other sizes. Something like...

select *
from Orders o
where
    exists (
        select d1.Id
        from OrderDetail d1
        where
            d1.Order_id = o.Id
            and d1.Size = @size)
    and not exists (
        select d2.Id
        from OrderDetail d2
        where
            d2.Order_id = o.Id
            and d2.Size <> @size);

We could take this answer another step further and translate this into a QueryOver query, but I don't want to spoil your fun. Is that enough to get you pointed in the right direction?

Upvotes: 1

Related Questions