How to use SQL Count() in QueryOver

Question

I'm trying to perform some simple SQL (using sql server 2005) using QueryOver in NHibernate. The query is to count how many times a serial number is repeatedly used in a list of items and then to select the serial numbers with only 1 use. Note that I don't want distinct serial numbers since I don't want to use serial numbers that have >1 use.

The SQL query to do this is:

SELECT SERNUM, expr1
FROM (SELECT SERNUM, COUNT(SERNUM) AS expr1
      FROM ITEM
      GROUP BY SERNUM) AS derivedtbl_1
WHERE (expr1 = 1)

What I have done so far using QueryOver is:

var query = session.QueryOver<Item>()
                        .Select(Projections.ProjectionList()
                        .Add(Projections.Count<Item>(x => x.Sernum))
                        .Add(Projections.GroupProperty("Sernum"))).List();

This code generates SQL (though it does not like putting the Count() column to a List). I'm not sure how to access the column of data generated by Count() to say only return where it is 1. The SQL that it does generates is:

SELECT   count(this_.SERNUM) as y0_,
         this_.SERNUM        as y1_
FROM     ITEM this_
GROUP BY this_.SERNUM

I'm not sure if I'm approaching this the right way but hopefully someone can point me to it.

.Add(Projections.Count<Item>(x => x.Sernum).As("count"))

Firo
– Firo

2011-09-16 12:15:38 +00:00
Commented Sep 16, 2011 at 12:15 — Firo
– Firo, Commented Sep 16, 2011 at 12:15

Firo · Accepted Answer · 2011-09-19 12:03:51Z

3

var serials = session.QueryOver<Item>()
    .Where(Restrictions.Eq(Projections.Count<Item>(i => i.Id), 1));
    .Select(Projections.GroupProperty<Item>(i => i.Sernum))
    .List();

should generate something like

SELECT SERNUM FROM ITEM GROUP BY SERNUM HAVING COUNT(*) = 1

to get the items, do something like

var subquery = QueryOver.Of<Item>()
    .Where(Restrictions.Eq(Projections.Count<Item>(i => i.Id), 1));
    .Select(Projections.GroupProperty<Item>(i => i.Sernum));

var items = QueryOver.Of<Item>()
    .WithSubquery.Where(i => i.Sernum).In(subquery);
    .List();

edited Sep 19, 2011 at 12:03

answered Sep 16, 2011 at 12:25

Firo

30.8k4 gold badges57 silver badges97 bronze badges

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

1 Comment

Peadar Doyle Over a year ago

Thanks for that, other than a few tweaks that's exactly what I was looking for.

Collectives™ on Stack Overflow

How to use SQL Count() in QueryOver

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