NH-3203 - Where method on "group by into" doesn't have any effect on SQL query

[nhibernate-bot]

Alexey Zimarev created an issue — 27th June 2012, 13:15:29:

I use the Linq query

            var q = from o in session.Query<OrdreLinjeVerdier>()
                    where o.OrdreLinjeReserve > 0
                    group o by o.ProduktLager.ProduktLagerID into g
                    select new
                    {
                        ProduktLagerID = g.Key,
                        OrdreRes = g.Sum(x => x.OrdreLinjeReserve),
                        OrdreResUtenSpesial = g.Where(x => x.LeveringsType.LeveringsTypeID == 1).Sum(x => (double?)x.OrdreLinjeReserve),
                        OrdreResKunGodkjent = g.Where(x => x.LeveringsType.LeveringsTypeID == 1 && x.OrdreHoldStatus.OrdreHoldStatusID == 1).Sum(x => (double?)x.OrdreLinjeReserve)
                    };

Which translates to the SQL query

    select 
        ordrelinje0*.ProduktLagerID as col_0_0*, 
        cast(sum(ordrelinje0*.OrdreLinjeReserve) as DECIMAL(19,5)) as col_1_0*, 
        cast(sum(ordrelinje0*.OrdreLinjeReserve) as DOUBLE PRECISION) as col_2_0*, 
        cast(sum(ordrelinje0*.OrdreLinjeReserve) as DOUBLE PRECISION) as col_3_0* 
    from 
        OrdreLinjeVerdier ordrelinje0_ 
    where 
        ordrelinje0_.OrdreLinjeReserve>@p0 
    group by 
        ordrelinje0_.ProduktLagerID;
    @p0 = 0 <Type: Decimal (0)>

As I can see Where method calls doesn't have any effect on the query when I expect it to produce CASE WHEN in the SQL query. When I use Projections.Conditional with Restrictions.Where it works fine.

---

Alexander Zaytsev added a comment — 9th March 2013, 10:40:51:

Could you please check if it works in master

---

Bohdan Makohin added a comment — 12th November 2014, 12:23:40:

Doesn't work as of NHibernate 4.0.0.4000

[acarroll-trend]

This recently bit our development team. It seems to be an issue with the .Sum() aggregate, because it works as expected with .Count(). You can work around it by using a ternary in the .Sum(). This is a little closer to the CASE statement that you'd write in SQL.

OrdreResUtenSpesial = (double?)g.Sum(x => x.LeveringsType.LeveringsTypeID == 1 ? x.OrdreLinjeReserve : 0d) ?? 0d,

Although there is a workaround, it would be great to see some movement on this, because it is against expectations. It will work and produce the incorrect result. You'd almost rather have a hard failure.