CODEWITHSUNDEEP
c#linqgroup-by
kbaccouche
kbaccouche

Reputation: 4605

GroupBy DayOfWeek in Linq

I have this Linq query which works fine

var test = (from c in context.ConsumptionSet
            join pi in context.PropertiesInstanceSet on c.PropertiesInstanceID equals pi.PropertiesInstanceID
            join ep in context.EquipmentPropertiesSet on pi.EquipmentPropertiesID equals ep.EquipmentPropertiesID
            where (ep.EquipmentID == equipmentId && pi.ProprietesName == ProprietesName.Energy && c.Date <= DateTime.Today && c.Date >= EntityFunctions.AddDays(DateTime.Today, -7))
            group c by c.Date.Day into grp
            select new
            {
               test = grp.Key,
               value = (from c2 in grp select c2.Value).Max()

            }).ToList();

But I want to group these results by the DayOfWeek Property, and it seems like Linq doesn't allow it since I get this error when I replace group c by c.Date.Day by group c by c.Date.DayOfWeek : The specified type member 'DayOfWeek' is not supported in LINQ to Entities

Is there any workaround to this problem ?

Upvotes: 1

Views: 1805

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Use SqlFunctions.DatePart:

var test = (from c in context.ConsumptionSet
            join pi in context.PropertiesInstanceSet on c.PropertiesInstanceID equals pi.PropertiesInstanceID
            join ep in context.EquipmentPropertiesSet on pi.EquipmentPropertiesID equals ep.EquipmentPropertiesID
            where (ep.EquipmentID == equipmentId && pi.ProprietesName == ProprietesName.Energy && c.Date <= DateTime.Today && c.Date >= EntityFunctions.AddDays(DateTime.Today, -7))
            group c by SqlFunctions.DatePart("weekday", c.Date) into grp
            select new
            {
               test = grp.Key,
               value = (from c2 in grp select c2.Value).Max()

            }).ToList();

Upvotes: 6

Related Questions