How to group by records from database based on date (day) c#?

devludo 0
Viewed 52

I know this could be a possible duplicate question, pardon me if it is.

Is there a way to GroupBy all the records from the database by date?

So:

  • say i have multiple records for this date 22/05/2022
  • and say i have multiple records from this date: 23/05/2022

Can i group all the records based on date parameter 22/05 and 23/05?

So that i would end up with a list containing n list for each day.

Here is what i did:

var grpQuery = await ctx.Registration.GroupBy(c => c.DateReference.Day).ToListAsync();

Where:

  • Registration is my table from where i am pulling the data
  • DateReference is a Date object containing the date

But i am getting this error "the linq expession could not be translated".

Can somone give me some advice on this?

EDIT

I tried this but it seems not to load any data, even setting break a break point will not return anything:

                var grpQuery = await query.GroupBy(d => new { DateReference = d.DateReference.Date }).Select(c => new RegistrationViewModel()
 {
   RegistrationId = c.FirstOrDefault().RegistrationId,
   PeopleId = c.FirstOrDefault().PeopleId,
   DateReference = c.Key.DateReference,
   DateChange = c.FirstOrDefault().DateChange,
   UserRef = c.FirstOrDefault().UserRef,
   CommissionId = c.FirstOrDefault().CommissionId,
   ActivityId = c.FirstOrDefault().ActivityId,
   MinuteWorked = c.FirstOrDefault().MinuteWorked,
 }).OrderBy(d => d.DateReference).ToListAsync();

Where:

  • RegistrationViewModel contains all those properties including DateReference
  • If i call the method using the API is stuck at "pending"
linq entity-framework c# database
2 Answers

First, don't. Even if the query was fixed, the equivalent query in the database would be GROUP BY DATEPART(day,registration.Date) which can't use indexes and therefore is slow.

According to the docs the equivalent of DATEPART(day, @dateTime) is dateTime.Day. The query still needs to have a proper Select though.

A correct query would be :

counts = ctx.Registrations.GroupBy(r=>r.RegistrationDate.Day)
                 .Select(g=>new {Day=g.Key,Count=g.Count())
                 .ToList();

The equivalent, slow query would be

SELECT DATEPART(day,registration.Date) as Day,count(*)
FROM Registrations
GROUP BY DATEPART(day,registration.Date)

Things get worse if we have to eg filter by date. The query would have to scan the entire table because it wouldn't be able to use any indexes covering the Date column

SELECT DATEPART(day,registration.Date) as Day,count(*)
FROM Registrations
WHERE Date >'20220901'
GROUP BY DATEPART(day,registration.Date)

Imagine having to scan 10 years of registrations only to get the current month.

This is a reporting query. For date related reports, using a prepopulated Calendar table can make the query infinitely easier.

SELECT Calendar.Day,COUNT(*)
FROM Registrations r
INNER JOIN Calendar on r.RegistrationDate=Calendar.Date
GROUP BY Calendar.Day

or

SELECT Calendar.Year, Calendar.Semester, Calendar.Day,COUNT(*)
FROM Registrations r
INNER JOIN Calendar on r.RegistrationDate=Calendar.Date
WHERE Calendar.Year = @someYear
GROUP BY Calendar.Year, Calendar.Semester,Calendar.Day

A Calendar table or Date dimension is a table with prepopulated dates, years, months, semesters or any other reporting period along with their names or anything needed to make reporting easier. Such a table can contain eg 10 or 20 years of data without taking a lot of space. To speed up queries, the columns can be aggressively indexed without taking too much extra space.

Doing the same in EF Core requires mapping Calendar as an entity and performing the JOIN in LINQ. This is one of the cases where it makes no sense to add a relation between entities :

var query=from registration in ctx.Registrations
          join date in Calendar 
              on registration.Date equals Calendar.Date
          group registration by date.Day into g
          select new { Day=g.Key, Count=g.Count()};
var counts = query.ToList();
Panagiotis Kanavos0

If you are using EF Core Please try this:

var grpQuery = await ctx.Registration.Select(a=>new {Re = a, G = (EF.Functions.DateDiffDay(a.End,DateTime.Today))}).ToListAsync().ContinueWith(d=>d.Result.GroupBy(a=>a.G));
SBI0
Related