nHibernate QueryOver Having clause with OR condition

Question

How to achieve the below query in nHibernate using queryover

Query

SELECT [DepartmentID]  ,COUNT(courseId)
FROM  [Course]
where [DepartmentID] >1
GROUP BY [DepartmentID]
hAVING COUNT(courseId) = 2 or  COUNT(courseId) = 3;

Table structure

    CREATE TABLE [dbo].[Course](
[CourseID] [int] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,

C# code i tried

Collection<Course> courseList = new Collection<Course>();
using (var session = NHibernateHelper.OpenSession("ConnectionName1"))
{
   Course courseAlias = null;
   SimpleExpression e1 = Restrictions.Ge(Projections.Count(Projections.Property(() => courseAlias.CourseID)), 2);
   SimpleExpression e2 = Restrictions.Ge(Projections.Count(Projections.Property(() => courseAlias.CourseID)), 3);
   var results2 = session.QueryOver<Course>(() => courseAlias)
                  .Where(e1||e2)
                  .SelectList(list => list
            .SelectGroup(x => x.DepartmentID).WithAlias(() => courseAlias.DepartmentID)
            .SelectCount(x => x.CourseID).WithAlias(() => courseAlias.CourseID)
             ).TransformUsing(Transformers.AliasToBean<Course>()).List<Course>();
}

Could you show us code snippet you've tried? What do you have so far? there is some inspiartion: http://stackoverflow.com/questions/20528760/ and doc: http://nhforge.org/doc/nh/en/index.html#queryqueryover — Radim Köhler, Jun 16 '14 at 10:24

score 0 · Accepted Answer · answered Jun 16 '14 at 11:00

0

It could look like this

Course courseAlias = null;
var query = session.QueryOver<Course>(() => courseAlias)
    .SelectList(l => l
    .SelectGroup(item => item.DepartmentID).WithAlias( () => courseAlias.DepartmentID)
    .SelectCount(item => item.StatusID).WithAlias(() => courseAlias.StatusID)
    )
    // WHERE Clause
    .Where(item => item.DepartmentID > 1)
    // HAVING Clause
    .Where( Restrictions.In(
        Projections.Count<Course>(item => item.StatusID)
        , new List<int> {2, 3})
    )
    .TransformUsing(Transformers.AliasToBean<Course>())

var list = query
    // take skip...
    .List<Course>();

answered Jun 16 '14 at 11:00

Radim Köhler

122,561
47
239
335

suppose if we have a scenario like having clause contains mixed of 'and' ,'or' condition how to handle it – san Jun 16 '14 at 11:08
Not sure if these would be possible... but in general, my answer give you some hints how to do it in common... Play with, try to experiment with `Restrictions.Or`... `Restrictions.Disjunction()`... but HAVING clause really requires some special handling, not all wil be accepted. But your requirement the answered snippet will solve ;) – Radim Köhler Jun 16 '14 at 11:11
1

I am having the similar problem. Need conditions in where and in having clause but it all appears in where result in error in sql. Tried this solution but queryover hangs it. Used like this .Where(WhereConjunction) .Where(HavingConjunction) separate. Not working. – Builder Apr 10 '15 at 15:12
@qazifarhan I would suggest, ask question with more details, you will get your answer. If I will know, I will try to assist... – Radim Köhler Apr 10 '15 at 15:14
OK will post question in few minutes. My Query is very big and complex so cannot put every thing. But will try in few minutes. Thanks – Builder Apr 10 '15 at 15:34
@qazifarhan Well, be smart, create question which one could reproduce and give you answer. Huge anything will not work... none will touch it... be smart ;) – Radim Köhler Apr 10 '15 at 15:35
http://stackoverflow.com/questions/29565783/nhibernate-queryover-criteria-appearing-in-where-instead-in-having-clause-err – Builder Apr 10 '15 at 15:55

nHibernate QueryOver Having clause with OR condition

Query

1 Answers1