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; }

}

Answer 1

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);
     }

Answer 2

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.