how to group based on dates then hours?

Question

I have a table which stores visit days in a fair. for example visitors visit fairs from day "a" to day "b". I wrote a query winch groups based on visit date. but it gives me "how many visits per day".

here is my query:

var visitDays = from v in _dbContext.VisitorEvents
                         join e in _dbContext.VisitorEvents on v.VisitorId equals e.VisitorId
                         where e.EventId == eventId
                         group v by EntityFunctions.TruncateTime(v.VisitDay) into g
                         select new StatisticItemDto()
                         {
                             Name = g.Key.ToString(),
                             Value = g.Count()
                         };
         total =visitDays.Any()? visitDays.Sum(x => x.Value):0;
         foreach (var item in visitDays)
         {
             item.Percent = Framework.Utility.GetPercent(item.Value, total);
         }

but I want more than this query? I mean amount of visitor In each hour a day. from example day 1 from 9:00 AM to 17:00 PM. "how many visit in every hour in each day" and for other days too. I hope my question be clear?

 public class StatisticItemDto
{
    public string Name { get; set; }
    public int Value { get; set; }
    public int Percent { get; set; }
    public int Total { get; set; }
    public List<StatisticItemDto> Hours { get; set; }

}

score 0 · Answer 1 · edited Jan 07 '17 at 08:37

0

Well the principal is the same. You are removing the whole time part in grouping with EntityFunctions.TruncateTime(v.VisitDay) but, you should just remove the minutes and seconds onward.

Use this I hope it helps (I assume that the VisitDay property contains DateTime values of the visits.):

var visitDays = from v in _dbContext.VisitorEvents
                     join e in _dbContext.VisitorEvents on v.VisitorId equals e.VisitorId
                     where e.EventId == eventId
                     group v by EntityFunctions.AddMicroseconds(v.VisitDay, -(int)v.VisitDay) into g
                     select new StatisticItemDto()
                     {
                         Name = g.Key.ToString(),
                         Value = g.Count()
                     };
     total =visitDays.Any()? visitDays.Sum(x => x.Value):0;
     foreach (var item in visitDays)
     {
         item.Percent = Framework.Utility.GetPercent(item.Value, total);
     }

edited Jan 07 '17 at 08:37

Afsaneh Daneshi

340
4
17

answered Jan 07 '17 at 08:34

Emad

3,809
3
32
44

If the cast to int didn't work try `-v.VisitDay.Ticks` instead. – Emad Jan 07 '17 at 08:39
dear @emad I want Group by date then in each date group based on hour. – Afsaneh Daneshi Jan 07 '17 at 08:44
@Afsaneh I don't understand what you mean by that? If you have the minor grouping you have the major one. For example you already have (5, 7, 4) for three days. Now you will get (1,0,0,2,2, 5,1,0,0,1, 1,1,2,0,0) for each hour You need the results for each hour – Emad Jan 07 '17 at 09:01
I want to get 4 chart for 4 days. and get amount of visitor in each hour a day. – Afsaneh Daneshi Jan 07 '17 at 09:06
I'm sorry I can't help you. My personal offer to you is that you should take a different approach by bringing different lists for each hour and then group them in days inside memory. – Emad Jan 07 '17 at 09:08

Gert Arnold · Answer 2 · 2017-01-07T21:29:25.620

I want Group by date then in each date group based on hour.

So you have to use GroupBy twice, the first time to get groups of days, a second time to get groups of hours within each day:

var visitDays = from v in _dbContext.VisitorEvents
                join e in _dbContext.VisitorEvents on v.VisitorId equals e.VisitorId
                where e.EventId == eventId
                group v by EntityFunctions.TruncateTime(v.VisitDay) into g
                select new
                {
                    Name = g.Key.ToString(),
                    Value = g.GroupBy(v => SqlFunctions.DatePart("HH", v.VisitDay))
                             .Select(h => h.Key, Count = h.Count())
                };

Not that I use System.Data.Entity.SqlServer.SqlFunctions here, because it contains the DatePart function that maps to the canonical function DATEPART. Also, you use EntityFunctions. If you're in a newer version of EF, you should change this into DbFunctions.

I leave it to you to look at the structure of the returned anonymous type and maybe decide that you want to redefine StatisticItemDto.

how to group based on dates then hours?

2 Answers2