CODEWITHSUNDEEP
c#linqchartsgroup-by
Roxy'Pro
Roxy'Pro

Reputation: 4444

LINQ - GroupBy Year/Month

I'm trying to populate graph data with total amount(sum) by a last four months, and visually it would look like this:

enter image description here

I've tried so far to group data by year and by a month, but I'm not sure if it's right approach cuz this doesn't work..

Here is the code:

 var testQUERY = await _context.Calculation
             .AsNoTracking()
             .Where(x => (x.PaymentDate != null && x.PaymentDate > DateTime.UtcNow.AddMonths(-4)))
            .GroupBy(x => new { x.PaymentDate.Value.Year, x.PaymentDate.Value.Month}).ToListAsync();

Here's my paymentDate : enter image description here

And I'm wondering how could I group by month only.. Error I'm facing is next:

Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'GroupBy(new <>f__AnonymousType0`2(Year = Convert([p].PaymentDate, DateTime).Year, Month = Convert([p].PaymentDate, DateTime).Month), [p])' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

P.S If I better think because I'm using 

x.PaymentDate != null && x.PaymentDate > DateTime.UtcNow.AddMonths(-4)

I don't need new anonymous type where I included year also.. but obliviusly I'm trying to group by column which does not exist.. ?

Upvotes: 0

Views: 1047

Answers (1)

Euphoric
Euphoric

Reputation: 12849

Try using this one. See comments for possible fixes.

var testQUERY = await _context.Calculation
         .AsNoTracking()
         .Where(x => x.PaymentDate != null)
         .Select(x => new { PaymentDate = x.PaymentDate.Value, Row=x }) // pre-select non-null payment date
         .Where(x => x.PaymentDate > DateTime.UtcNow.AddMonths(-4)) // this should go after the grouping, as it might include from just part of the month
         .GroupBy(x => new { x.PaymentDate.Year, x.PaymentDate.Month})
         .Select(grp=> new { grp.Key.Year, grp.Key.Month, Count = grp.Count()) // flatten group and calculate aggregates
         .ToListAsync();

Upvotes: 2

Related Questions