CODEWITHSUNDEEP
sqlnhibernatefluent-nhibernatequeryover
Mark
Mark

Reputation: 1554

NHibernate Query subquery group by join back to main entity

Hello i'm trying to achieve the following SQL

SELECT this_.*
 FROM WorkItems this_ 
 WHERE this_.UserId = (

 SELECT this_1_.y0_ as y0_
 FROM
 (
    SELECT top 1 this_0_.UserId as y0_, count(this_0_.UserId) as y1_ 
    FROM WorkItems this_0_ 
    WHERE this_0_.StateId = 1 and this_0_.Type like 'Data' 
    GROUP BY this_0_.UserId 
    ORDER BY y1_ desc
  ) this_1_

);

so far i have manged to produce something fairly close. I'm missing the part where i just select out the userId. heres the query so far

var subquery = QueryOver.Of<WorkItem>().Where(w => w.State == state)
                                .AndRestrictionOn(w => w.Type).IsLike(type, MatchMode.Exact)
                                .SelectList(list =>
                                            list.SelectGroup(w => w.UserId)
                                                .WithAlias(() => resultAlias.userId)
                                                .SelectCount(w => w.UserId)
                                                .WithAlias(() => resultAlias.count))
                                .OrderByAlias(() => resultAlias.count)
                                .Desc().Take(1);

        var query =
            CurrentSession.QueryOver<WorkItem>()
                          .WithSubquery
                          .WhereProperty(p => p.UserId)
                          .Eq(subquery);

Upvotes: 1

Views: 1777

Answers (1)

Gary
Gary

Reputation: 111

Try this: -

var subquery = QueryOver.Of<WorkItem>().Where(w => w.State == state)
                .AndRestrictionOn(w => w.Type).IsLike(type, MatchMode.Exact)
                .SelectList(list => list.SelectGroup(w => w.UserId))
                    .OrderBy(Projections.Count<WorkItem>(x => x.UserId))
                .Desc().Take(1);

var query = CurrentSession.QueryOver<WorkItem>()
              .WithSubquery
              .WhereProperty(p => p.UserId)
              .Eq(subquery);

Upvotes: 1

Related Questions