I’m new to .NET Core and working on a project where I need to group data by week using Entity Framework Core (EF Core) with a MySQL database. However, I’m encountering an issue with the grouping.

Here’s the LINQ query I’m using:

from report in _context.DmarcReports
.Where(r => r.DomainName == request.Domain)
.Where(r => !request.BeginDate.HasValue || r.BeginDate >= request.BeginDate.Value)
.Where(r => !request.EndDate.HasValue || r.EndDate <= request.EndDate.Value)
join summary in _context.DmarcSummary on report.ReportId equals summary.ReportId
join sourceLookup in _context.SourceLookup on summary.SourceIp equals sourceLookup.Ip
let week = EF.Functions.DateDiffWeek(
  EF.Functions.DateFromParts(report.BeginDate.Year, 1, 1), 
  report.BeginDate
) + 1  
let year = report.BeginDate.Year
group summary by new { sourceLookup.SourceName, Week = week, Year = year } into g
select new
{
    Date = EF.Functions.DateFromParts(g.Key.Year, 1, 1),
    Date = new DateTime(g.Key.Year, 1, 1).AddDays((g.Key.Week - 1) * 7),
    SourceName = g.Key.SourceName,
    DMARCPassCount = g.Sum(x => x.DMARCVolume),
}

I’m trying to use EF.Functions.DateDiffWeek() to calculate the week number for grouping. However, I’m getting the following error:

'DbFunctions' does not contain a definition for 'DateDiffWeek' and no accessible extension method 'DateDiffWeek' accepting a first argument of type 'DbFunctions' could be found (are you missing a using directive or an assembly reference?)

I’ve already added the necessary using directives:

using Microsoft.EntityFrameworkCore;
using Pomelo.EntityFrameworkCore.MySql;

Any suggestions on how to fix this issue?