CODEWITHSUNDEEP
nhibernatequeryover
Peadar Doyle
Peadar Doyle

Reputation: 1112

How to use SQL Count() in QueryOver

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.

Upvotes: 3

Views: 2132

Answers (1)

Firo
Firo

Reputation: 30813

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();

Upvotes: 3

Related Questions