The LINQ Expression 'DbSet<> Could not be translated method 'System.DateTime.ToString' failed, After Migrated from ASP.NET Core 2.1 to .NET6

Question

I have migrated my project ASP.NET Core 2.1 to .NET6, but some of my LINQ Query do not work properly and give the below error:

System.InvalidOperationException Message=The LINQ expression 'DbSet() .Where(p => p.PaymentDate.ToString("MMM-yyyy").Equals(__ToString_0) && p.PaymentType == "Treatment Fee")' could not be translated. Additional information: Translation of method 'System.DateTime.ToString' failed. If this method can be mapped to your custom function, Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Here is my Home Controller Code in ASP.NET Core 2.1 which returns data from Database As a Monthly and Daily report and displays them in a Line chart and ViewBag. This Code is working fine with asp.net core 2.1, but after I migrated to .NET6 it gives an error for each line of LINQ Query Code that I used Date String Formatting for Example: a.RegisterDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy")

Here Is my Home Controller code in ASP.NET Core 2.1 That Need to be modified in .NET6 LINQ Query Format, please help me to modify all this code from asp.net core 2.1 to .NET6 LINQ Query, Thank You.

public class HomeController : BaseController
{
    private readonly IMvcControllerDiscovery _mvcControllerDiscovery;
    private readonly IWebHostEnvironment _hostingEnvironment;
    public HomeController(HoshmandDBContext context, IWebHostEnvironment hostingEnvironment, IMvcControllerDiscovery mvcControllerDiscovery) : base(context)
    {
        _hostingEnvironment = hostingEnvironment;
        _mvcControllerDiscovery = mvcControllerDiscovery;
    }
    public IActionResult Index(DateTime? date = null)
    {
        date = date ?? GetLocalDateTime();
        ViewBag.date = date;
        // MonthlyTransectionList(date);
        ViewBag.CompletedPatients = _context.PatientTbs.Where(a => a.PatientStatus == "Completed" && !a.IsDeleted && a.RegisterDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Count();

        string[] monthName = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
        // patient chart registration
        ViewBag.PatientRegistrationStatisticsInCurrentYear = GetNumberOfPatientsPerMonthInCurrentYear(monthName);

        // transaction chart
        List<IncomeAndOutcome> incomeAndOutcomes = new List<IncomeAndOutcome>();
        var IncomePerMonthInCurrentYear = GetIncomePerMonthInCurrentYear(monthName);
        var OutcomePerMonthInCurrentYear = GetOutcomePerMonthInCurrentYear(monthName);
        foreach (var item in IncomePerMonthInCurrentYear)
        {
            incomeAndOutcomes.Add(new IncomeAndOutcome
            {
                month = item.Key,
                Income = item.Value,
                Outcome = OutcomePerMonthInCurrentYear.FirstOrDefault(a => a.Key == item.Key).Value
            });
        }
        ViewBag.IncomeAndOutcomeStatisticsInCurrentYear = incomeAndOutcomes;
        var _todayAppointments = _context.AppointmentTbs
            .Where(a => !a.IsDeleted && a.AppointmentDate.Value.ToString("MMM-yyyy") == date.Value.ToString("MMM-yyyy"))
            .GroupBy(a => a.SesstionGroup);

        List<TodayAppointment> todayAppointments = new List<TodayAppointment>();
        if (_todayAppointments.Any(a => a.FirstOrDefault().AppointmentStatus == 4))
        {
            todayAppointments.Add(new TodayAppointment
            {
                status = "Pending",
                count = _todayAppointments.Where(a => a.FirstOrDefault().AppointmentStatus == 4).Count()
            });
        }
        else
        {
            todayAppointments.Add(new TodayAppointment
            {
                status = "Pending",
                count = 0
            });
        }
        if (_todayAppointments.Any(a => a.FirstOrDefault().AppointmentStatus == 1))
        {
            todayAppointments.Add(new TodayAppointment
            {
                status = "Completed",
                count = _todayAppointments.Where(a => a.FirstOrDefault().AppointmentStatus == 1).Count()
            });
        }
        else
        {
            todayAppointments.Add(new TodayAppointment
            {
                status = "Completed",
                count = 0
            });
        }
        if (_todayAppointments.Any(a => a.FirstOrDefault().AppointmentStatus == 3))
        {
            todayAppointments.Add(new TodayAppointment
            {
                status = "Canceled",
                count = _todayAppointments.Where(a => a.FirstOrDefault().AppointmentStatus == 3).Count()
            });
        }
        else
        {
            todayAppointments.Add(new TodayAppointment
            {
                status = "Canceled",
                count = 0
            });
        }
        ViewBag.TodayAppointments = todayAppointments;

        return View();
    }
    private IActionResult MonthlyTransectionList(DateTime? date = null)
    {
        ViewBag.MonthlyPatientIcomeTreatmentFee = _context.PatientPaymentHistories.Where(a => a.PaymentDate.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy")) && a.PaymentType == "Treatment Fee").Sum(a => a.PaidAmount);
        ViewBag.MonthlyPatientIcomeVisitFee = _context.PatientPaymentHistories.Where(a => a.PaymentDate.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy")) && a.PaymentType == "Checkup Fee").Sum(a => a.PaidAmount);
        ViewBag.MonthlyExpenseOut = _context.Expenses.Where(a => !a.IsDelete && a.ExpenseDate.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Sum(a => a.ExpenseAmount);
        ViewBag.MonthlyStockOut = _context.StockTransectionTbs.Where(a => a.TransectionDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Sum(a => a.TransectionAmount);
        ViewBag.MonthlyPatientRefunded = _context.PatientPaymentHistories.Where(a => a.PaymentDate.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy")) && a.PaymentType == "Refund").Sum(a => a.PaidAmount);
        ViewBag.MonthlyLabOut = _context.labsPayments.Where(a => a.PaymentDate.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Sum(a => a.TotalPaid);
        ViewBag.MonthlySalaryOut = _context.EmployeeTransectionTbs.Where(a => !a.IsDeleted && a.TransectionDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Sum(a => a.TransectionAmount);
        ViewBag.MonthlyDebitedTransectionOut = _context.OtherTransectionTbs.Where(a => !a.IsDeleted && a.TransectionType == "Debited" && a.TransectionDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Sum(a => a.TransectionAmount);
        ViewBag.MonthlyCreditedTransectionOut = _context.OtherTransectionTbs.Where(a => !a.IsDeleted && a.TransectionType == "Credited" && a.TransectionDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Sum(a => a.TransectionAmount);
        return View();
    }
    private PatientRegistrationStatisticsInCurrentYear GetNumberOfPatientsPerMonthInCurrentYear(string[] monthName)
    {
        var PatientsInCurrentYear = _context.PatientTbs.Where(a => !a.IsDeleted && a.IsActive.Value && a.RegisterDate.Value >= new DateTime(GetLocalDateTime().Year, 1, 1).Date);
        PatientRegistrationStatisticsInCurrentYear patientRegistrationStatisticsInCurrentYear = new PatientRegistrationStatisticsInCurrentYear
        {
            totalPatient = PatientsInCurrentYear.Count(),
            year = new DateTime(GetLocalDateTime().Year, 1, 1).Year,
            PatientPerMonth = new Dictionary<string, int>()
        };
        foreach (var item in monthName)
        {
            patientRegistrationStatisticsInCurrentYear.PatientPerMonth[item] = PatientsInCurrentYear.Where(a => a.RegisterDate.Value.ToString("MMM") == item).Count();
        }
        return patientRegistrationStatisticsInCurrentYear;
    }
    private Dictionary<string, decimal> GetIncomePerMonthInCurrentYear(string[] monthName)
    {
        Dictionary<string, decimal> Income = new Dictionary<string, decimal>();
        foreach (var month in monthName)
        {
            var income = _context.PatientPaymentHistories
                .Where(a => a.PaymentDate.Date >= new DateTime(GetLocalDateTime().Year, 1, 1) && !a.IsDeleted &&
                 !string.Equals(a.PaymentType, "Refund", StringComparison.CurrentCultureIgnoreCase)
                 && a.PaymentDate.ToString("MMM") == month).Sum(a => a.PaidAmount)
                + _context.OtherTransectionTbs.Where(a => a.TransectionDate.Value.Date >= new DateTime(GetLocalDateTime().Year, 1, 1)
                 && !a.IsDeleted && string.Equals(a.TransectionType, "Credited", StringComparison.CurrentCultureIgnoreCase)
                 && a.TransectionDate.Value.ToString("MMM") == month).Sum(a => a.TransectionAmount);
            Income.Add(month, income.Value);
        }
        return Income;
    }
    private Dictionary<string, decimal> GetOutcomePerMonthInCurrentYear(string[] monthName)
    {
        Dictionary<string, decimal> outcome = new Dictionary<string, decimal>();
        foreach (var month in monthName)
        {
            var og = _context.Expenses.Where(a => !a.IsDelete && a.ExpenseDate.Date >= new DateTime(GetLocalDateTime().Year, 1, 1) && a.ExpenseDate.ToString("MMM") == month).Sum(a => a.ExpenseAmount)
             + _context.StockTransectionTbs.Where(a => a.TransectionDate.Value.Date >= new DateTime(GetLocalDateTime().Year, 1, 1) && a.TransectionDate.Value.ToString("MMM") == month).Sum(a => a.TransectionAmount)
             + _context.PatientPaymentHistories.Where(a => a.PaymentDate.Date >= new DateTime(GetLocalDateTime().Year, 1, 1) && a.PaymentDate.ToString("MMM") == month && a.PaymentType == "Refund").Sum(a => a.PaidAmount)
             + _context.labsPayments.Where(a => a.PaymentDate.Date >= new DateTime(GetLocalDateTime().Year, 1, 1) && a.PaymentDate.ToString("MMM") == month).Sum(a => a.TotalPaid)
             + _context.EmployeeTransectionTbs.Where(a => !a.IsDeleted && a.TransectionDate.Value.Date >= new DateTime(GetLocalDateTime().Year, 1, 1) && a.TransectionDate.Value.ToString("MMM") == month).Sum(a => a.TransectionAmount)
             + _context.OtherTransectionTbs.Where(a => !a.IsDeleted && a.TransectionType == "Debited" && a.TransectionDate.Value.Date >= new DateTime(GetLocalDateTime().Year, 1, 1) && a.TransectionDate.Value.ToString("MMM") == month).Sum(a => a.TransectionAmount);
            outcome.Add(month, og.Value);
        }
        return outcome;
    }
}
public class PatientRegistrationStatisticsInCurrentYear
{
    public int year { get; set; }
    public int totalPatient { get; set; }
    public Dictionary<string, int> PatientPerMonth { get; set; }
}

public class IncomeAndOutcome
{
    public string month { get; set; }
    public decimal Income { get; set; }
    public decimal Outcome { get; set; }
}
public class TodayAppointment
{
    public string status { get; set; }
    public int count { get; set; }
}

Answer 1

EF Core 2 had automatic silent client side evaluation enabled, which was disabled for later versions - see the corresponding breaking change:

Old behavior

Before 3.0, when EF Core couldn't convert an expression that was part of a query to either SQL or a parameter, it automatically evaluated the expression on the client. By default, client evaluation of potentially expensive expressions only triggered a warning.

New behavior

Starting with 3.0, EF Core only allows expressions in the top-level projection (the last Select() call in the query) to be evaluated on the client. When expressions in any other part of the query can't be converted to either SQL or a parameter, an exception is thrown.

As a quick fix you explicitly evaluate on client side (via AsEnumerable or ToList and their async counterparts), but in general I would argue that you should consider rewriting queries so they are translated into SQL (based on your database you should look into supported function mappings, like here for SQL Server, based on exception message you should look into using correct datetime functions, which compare dateparts).

Answer 2

Your approach to filter by month is not translatable by EF Core. I would suggest to introduce extension method which will generate correct filter by month. And another benefit, if your tables has indexes on date - they will be used by Database server.

Sample of usage:

ViewBag.CompletedPatients = _context.PatientTbs.Where(a => a.PatientStatus == "Completed" && !a.IsDeleted)
   .FilterByMonth(date.Value, a => a.RegisterDate).Count();
ViewBag.CheckupPatients = _context.PatientTbs.Where(a => a.PatientStatus == "Checkup" && !a.IsDeleted)
   .FilterByMonth(date.Value, a => a.RegisterDate).Count();

Extension implemenation, it contains FilterByDay, FilterByMonth, FilterByYear and generic FilterByDateRange:

public static class QueryableExtensions
{
    public static IQueryable<T> FilterByDay<T>(this IQueryable<T> query, DateTime date, Expression<Func<T, DateTime?>> dateField)
    {
        var start = date.Date;
        var end   = start.AddDays(1);

        return query.FilterByDateRange(start, end, dateField);
    }

    public static IQueryable<T> FilterByMonth<T>(this IQueryable<T> query, DateTime date, Expression<Func<T, DateTime?>> dateField)
    {
        var start = new DateTime(date.Year, date.Month, 1);
        var end   = start.AddMonths(1);

        return query.FilterByDateRange(start, end, dateField);
    }

    public static IQueryable<T> FilterByYear<T>(this IQueryable<T> query, DateTime date, Expression<Func<T, DateTime?>> dateField)
    {
        var start = new DateTime(date.Year, 1, 1);
        var end   = start.AddYears(1);

        return query.FilterByDateRange(start, end, dateField);
    }

    public static IQueryable<T> FilterByDateRange<T>(this IQueryable<T> query, DateTime startInclusive,
        DateTime endExclusive, Expression<Func<T, DateTime?>> dateField)
    {
        var entityParam = dateField.Parameters[0];
        var fieldExpr   = dateField.Body;

        // e.DateField >= startInclusive && e.DateField < endExclusive 
        var filterExpression = Expression.AndAlso(
            Expression.GreaterThanOrEqual(fieldExpr, Expression.Constant(startInclusive, fieldExpr.Type)),
            Expression.LessThan(fieldExpr, Expression.Constant(endExclusive, fieldExpr.Type)));

        // e => e.DateField >= startInclusive && e.DateField < endExclusive 
        var filterLambda = Expression.Lambda<Func<T, bool>>(filterExpression, entityParam);
        return query.Where(filterLambda);
    }
}

Also I have noticed that you have used EF Core extremely ineffective. Access to the same table can be simplified:

var statistic = _context.PatientTbs
    .Where(a => !a.IsDeleted)
    .FilterByMonth(date.Value, a => a.RegisterDate)
    .GroupBy(a => 1) // by constant
    .Select(g => new
    {
        Completed = g.Count(x => x.PatientStatus == "Completed"),
        Checkup = g.Count(x => x.PatientStatus == "Checkup"),
        Working = g.Count(x => x.PatientStatus == "Working"),
        Closed = g.Count(x => x.PatientStatus == "Closed")
    })
    .FirstOrDefault();

ViewBag.CompletedPatients = statistic?.Completed ?? 0;
ViewBag.CheckupPatients = statistic?.Checkup ?? 0;
ViewBag.WorkingPatients = statistic?.Working ?? 0;
ViewBag.ClosedPatients = statistic?.Closed ?? 0;

Also for month statistic query can be executed once:

string[] monthName = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }; 

var patientsInCurrentYear = _context.PatientTbs.FilterByYear(date.Value, a => a.RegisterDate);

var statisticByMonth = patientsInCurrentYear
    .GroupBy(a => a.RegisterDate.Value.Month)
    .Select(g => new 
    {
        Month = g.Key,
        Count = g.Count();
    })
    .ToDictionary(x => x.Month);

for (var i = 0; i < monthName.Length; i++) 
{ 
    item = monthName[i];
    var count = 0;
    if (statisticByMonth.TryGetValue(i + 1, out var statistic))
        count = statistic.Count;

    patientRegistrationStatisticsInCurrentYear.PatientPerMonth[item] = count; 
}