CODEWITHSUNDEEP
nhibernatequeryover
Mike
Mike

Reputation: 4287

NHibernate select column with SqlFunction, group by same criteria

I need to query for a count based on each day as well as group by the same criteria. The generated query should look similar to

select SendTo, dateadd(dd,
                 0,
                 datediff(dd,
                          0,
                          WorkToBeginDate))
from Locates
group by SendTo, dateadd(dd,
                 0,
                 datediff(dd,
                          0,
                          WorkToBeginDate))

I'm currently using the query below but it does not group by the date.

var dateGroupBy = Projections.SqlFunction("date", NHibernateUtil.Date,
    Projections.Group<Domain.Locate>(g => g.WorkToBeginDate));

var stats = 
    _session.QueryOver<Domain.Locate>()
    .SelectList(x => x
        .SelectGroup(xx => xx.SendTo).WithAlias(() => statsDto.SentTo)
        .SelectCount(xx => xx.LocateId).WithAlias(() => statsDto.Count)
        .Select(dateGroupBy)
        .WithAlias(() => statsDto.DueDate))
    .TransformUsing(Transformers.AliasToBean<StatsDto>())
    .List<StatsDto>();

Executing this query yields

SELECT   this_.SendTo                             as y0_,
         count(this_.LocateId)                    as y1_,
         dateadd(dd,
                 0,
                 datediff(dd,
                          0,
                          this_.WorkToBeginDate)) as y2_
FROM     Locates this_
GROUP BY this_.SendTo,
         this_.WorkToBeginDate

I'm assuming it's because I'm using Select and not SelectGroup in my projections. I have tried .SelectGroup(xx => new SqlFunctionProjection("date", NHibernateUtil.Date, Projections.Group<Domain.Locate>(g => g.WorkToBeginDate))), however this gives me Could not determine member from new SqlFunctionProjection("date", NHibernateUtil.Date, new [] {Group(g => Convert(g.WorkToBeginDate))}).

Upvotes: 3

Views: 1462

Answers (1)

reggie
reggie

Reputation: 13721

You can nest the dateGroupBy value with another Projections.Group method.

var dateGroupBy = Projections.Group(Projections.SqlFunction("date", NHibernateUtil.Date,
Projections.Group<Domain.Locate>(g => g.WorkToBeginDate)));

That should give you the desired results. :)

Upvotes: 3

Related Questions