Order by date and Group by Day

Question

I have an sql Table that presents historic of enegy consumption values. Now I need to perform some queries in order to get statistics by hour, day, week, month and years, Of course each one apart. An example of query is :

from c in context.ConsumptionSet
join e in context.EquipmentSet on c.EquipmentID equals e.EquipmentID
orderBy c.Date
group new { c, e } by c.Date into grp
 select new
{
    date = grp.Key.Day,
    value = grp.GroupBy(i => i.e.EquipmentID).Sum(g => g.Max(i => i.c.Value))
})

A you can see, I want the list to be ordered by Date first ( which is of a DateTime type), and then grouped by day. However this above query did not provide me with the excepted result. What am I missing right there ?

exactly, I don't get the expected order, just random values.. so what am I supposed to in order to get the desired result ? — mahoosh, Aug 01 '13 at 11:56
SELECT sd.name, s.BuilderName, Min(Date) as MinDatetime, Max(Date) as MaxDatetime, datediff(day, min(Date), max(Date))/ cast (count(sd.spidername) as float) FROM [Data] sd join Example s on s.name= sd.Name where spiderjobid='NOT RUN' Group by sd.name, s.name How to convert this sql into LINQ ? — sara, Mar 09 '16 at 09:02

score 0 · Accepted Answer · answered Aug 01 '13 at 16:36

Assuming that you are storing values with date and time, then grouping by them will have little or no effect. To group, you need equal values to group on.

For example:

Id |  Date
---+------------------------
1  |  2013-01-01 1:23:45.333
2  |  2013-01-01 2:34:56.667

Grouping by Date will not help, because the values are different. Instead, I'd need to group by something more meaningful, perhaps by day.

To do this in Entity Framework, use the methods in the EntityFunctions or SqlFunctions classes. For example:

from foo in context.Whatever
group foo by EntityFunctions.TruncateTime(foo.Date) into grp
...

Now you are grouping by just the date. Since both rows are on 2013-01-01, they will be grouped together.

Order by date and Group by Day

1 Answers1