View does not group by datetime date

[jasonlaw]

Hi @rivantsov ,

The view group by does not work for DateTime.Date.

    private void RegisterViews()
    {
        var companySMS = ViewHelper.EntitySet<ICompanySMS>();

        var smsViewQuery = from sms in companySMS
                           orderby sms.Company.Id, sms.TransType, sms.CreatedOn.Date
                           group sms by new { CompanyId = sms.Company.Id, sms.TransType, sms.CreatedOn.Date } into g
                           select new
                           {
                               g.Key.CompanyId,
                               g.Key.Date,
                               g.Key.TransType,
                               Qty = g.Sum(s => s.Qty)
                           };

        RegisterView<ICompanySMSView>(smsViewQuery);
    }

Existing View Generated: select tx0$.Company_Id AS CompanyId,cast(tx0$.CreatedOn as date) AS Date,tx0$.TransType AS TransType,sum(tx0$.Qty) AS Qty from vrewards_dev1.CompanySMS tx0$ group by tx0$.Company_Id,tx0$.TransType,tx0$.CreatedOn order by tx0$.Company_Id,tx0$.TransType,cast(tx0$.CreatedOn as date)

Expected (notes the bold CreatedOn as date): select tx0$.Company_Id AS CompanyId,cast(tx0$.CreatedOn as date) AS Date,tx0$.TransType AS TransType,sum(tx0$.Qty) AS Qty from vrewards_dev1.CompanySMS tx0$ group by tx0$.Company_Id,tx0$.TransType,cast(tx0$.CreatedOn as date) order by tx0$.Company_Id,tx0$.TransType,cast(tx0$.CreatedOn as date)

[rivantsov]

All right, sorry for your troubles, I made some experiments, and it looks like the query engine does not support expressions in GroupBy at all, only plain columns; don't know why it happened, will create a work item for this. However the Date function works correctly in the SELECT output clause, just ignored in GroupBy For now, suggest a workaround that might work. By the way, OrderBy before Group By is moot, does not make sense to sort before grouping which will work across order. Workaround: create 2 views: the first view without GroupBy, just select the columns you need, and select CreatedDate = CreatedOn.Date Now in the second view select from the first view and do GroupBy. this should work

[jasonlaw]

Thanks for the workaround, it works fine now. :)

[rivantsov]

this is now fixed in v 4.1