ASP.NET Core 2.1迁移.NET6后LINQ DateTime.ToString翻译失败求助

问题原因 #

报错源于EF Core 6默认禁用了隐式客户端求值，而ASP.NET Core 2.1使用的旧版EF Core会自动将无法翻译的DateTime.ToString()操作切换到客户端执行。EF Core 6要求所有LINQ查询必须能被翻译成SQL，否则直接抛出异常。

解决思路 #

• 移除LINQ查询中所有DateTime.ToString("MMM-yyyy")或DateTime.ToString("MMM")操作
• 直接通过比较日期的Year和Month属性筛选数据，这类属性可被EF Core翻译成SQL日期函数
• 按月份分组统计时，将月份缩写映射为对应数字，再匹配日期的Month属性

修改后的完整代码 #

public class HomeController : BaseController
{
    private readonly IMvcControllerDiscovery _mvcControllerDiscovery;
    private readonly IWebHostEnvironment _hostingEnvironment;
    // 月份缩写到数字的映射字典
    private static readonly Dictionary<string, int> _monthNameToNumber = new Dictionary<string, int>
    {
        {"Jan", 1}, {"Feb", 2}, {"Mar", 3}, {"Apr", 4}, {"May", 5}, {"Jun", 6},
        {"Jul", 7}, {"Aug", 8}, {"Sep", 9}, {"Oct", 10}, {"Nov", 11}, {"Dec", 12}
    };

    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;
        // 替换ToString格式化：直接比较年月
        ViewBag.CompletedPatients = _context.PatientTbs
            .Where(a => a.PatientStatus == "Completed" && !a.IsDeleted 
                && a.RegisterDate.Value.Year == date.Value.Year 
                && a.RegisterDate.Value.Month == date.Value.Month)
            .Count();

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

        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.TryGetValue(item.Key, out var outcome) ? outcome : 0
            });
        }
        ViewBag.IncomeAndOutcomeStatisticsInCurrentYear = incomeAndOutcomes;

        // 替换ToString格式化：直接比较年月
        var _todayAppointments = _context.AppointmentTbs
            .Where(a => !a.IsDeleted 
                && a.AppointmentDate.Value.Year == date.Value.Year 
                && a.AppointmentDate.Value.Month == date.Value.Month)
            .GroupBy(a => a.SesstionGroup);

        List<TodayAppointment> todayAppointments = new List<TodayAppointment>();
        // 优化：提前统计状态数量，避免多次遍历分组
        var statusCounts = _todayAppointments
            .Select(g => g.FirstOrDefault())
            .Where(a => a != null)
            .GroupBy(a => a.AppointmentStatus)
            .ToDictionary(g => g.Key, g => g.Count());

        todayAppointments.Add(new TodayAppointment
        {
            status = "Pending",
            count = statusCounts.TryGetValue(4, out var pending) ? pending : 0
        });
        todayAppointments.Add(new TodayAppointment
        {
            status = "Completed",
            count = statusCounts.TryGetValue(1, out var completed) ? completed : 0
        });
        todayAppointments.Add(new TodayAppointment
        {
            status = "Canceled",
            count = statusCounts.TryGetValue(3, out var canceled) ? canceled : 0
        });

        ViewBag.TodayAppointments = todayAppointments;

        return View();
    }

    private IActionResult MonthlyTransectionList(DateTime? date = null)
    {
        date = date ?? GetLocalDateTime();
        int targetYear = date.Value.Year;
        int targetMonth = date.Value.Month;

        // 所有ToString格式化替换为年月比较
        ViewBag.MonthlyPatientIcomeTreatmentFee = _context.PatientPaymentHistories
            .Where(a => a.PaymentDate.Year == targetYear && a.PaymentDate.Month == targetMonth && a.PaymentType == "Treatment Fee")
            .Sum(a => a.PaidAmount) ?? 0;

        ViewBag.MonthlyPatientIcomeVisitFee = _context.PatientPaymentHistories
            .Where(a => a.PaymentDate.Year == targetYear && a.PaymentDate.Month == targetMonth && a.PaymentType == "Checkup Fee")
            .Sum(a => a.PaidAmount) ?? 0;

        ViewBag.MonthlyExpenseOut = _context.Expenses
            .Where(a => !a.IsDelete && a.ExpenseDate.Year == targetYear && a.ExpenseDate.Month == targetMonth)
            .Sum(a => a.ExpenseAmount) ?? 0;

        ViewBag.MonthlyStockOut = _context.StockTransectionTbs
            .Where(a => a.TransectionDate.Value.Year == targetYear && a.TransectionDate.Value.Month == targetMonth)
            .Sum(a => a.TransectionAmount) ?? 0;

        ViewBag.MonthlyPatientRefunded = _context.PatientPaymentHistories
            .Where(a => a.PaymentDate.Year == targetYear && a.PaymentDate.Month == targetMonth && a.PaymentType == "Refund")
            .Sum(a => a.PaidAmount) ?? 0;

        ViewBag.MonthlyLabOut = _context.labsPayments
            .Where(a => a.PaymentDate.Year == targetYear && a.PaymentDate.Month == targetMonth)
            .Sum(a => a.TotalPaid) ?? 0;

        ViewBag.MonthlySalaryOut = _context.EmployeeTransectionTbs
            .Where(a => !a.IsDeleted && a.TransectionDate.Value.Year == targetYear && a.TransectionDate.Value.Month == targetMonth)
            .Sum(a => a.TransectionAmount) ?? 0;

        ViewBag.MonthlyDebitedTransectionOut = _context.OtherTransectionTbs
            .Where(a => !a.IsDeleted && a.TransectionType == "Debited" && a.TransectionDate.Value.Year == targetYear && a.TransectionDate.Value.Month == targetMonth)
            .Sum(a => a.TransectionAmount) ?? 0;

        ViewBag.MonthlyCreditedTransectionOut = _context.OtherTransectionTbs
            .Where(a => !a.IsDeleted && a.TransectionType == "Credited" && a.TransectionDate.Value.Year == targetYear && a.TransectionDate.Value.Month == targetMonth)
            .Sum(a => a.TransectionAmount) ?? 0;

        return View();
    }

    private PatientRegistrationStatisticsInCurrentYear GetNumberOfPatientsPerMonthInCurrentYear(string[] monthName)
    {
        int currentYear = GetLocalDateTime().Year;
        var PatientsInCurrentYear = _context.PatientTbs
            .Where(a => !a.IsDeleted && a.IsActive.Value && a.RegisterDate.Value.Year == currentYear);

        PatientRegistrationStatisticsInCurrentYear patientRegistrationStatisticsInCurrentYear = new PatientRegistrationStatisticsInCurrentYear
        {
            totalPatient = PatientsInCurrentYear.Count(),
            year = currentYear,
            PatientPerMonth = new Dictionary<string, int>()
        };

        foreach (var item in monthName)
        {
            int monthNumber = _monthNameToNumber[item];
            // 替换ToString("MMM")：通过月份数字匹配
            patientRegistrationStatisticsInCurrentYear.PatientPerMonth[item] = PatientsInCurrentYear
                .Where(a => a.RegisterDate.Value.Month == monthNumber)
                .Count();
        }
        return patientRegistrationStatisticsInCurrentYear;
    }

    private Dictionary<string, decimal> GetIncomePerMonthInCurrentYear(string[] monthName)
    {
        Dictionary<string, decimal> Income = new Dictionary<string, decimal>();
        int currentYear = GetLocalDateTime().Year;

        foreach (var month in monthName)
        {
            int monthNumber = _monthNameToNumber[month];
            // 替换ToString("MMM")：通过月份数字匹配
            var patientIncome = _context.PatientPaymentHistories
                .Where(a => a.PaymentDate.Year == currentYear && !a.IsDeleted 
                    && !string.Equals(a.PaymentType, "Refund", StringComparison.CurrentCultureIgnoreCase)
                    && a.PaymentDate.Month == monthNumber)
                .Sum(a => a.PaidAmount) ?? 0;

            var otherIncome = _context.OtherTransectionTbs
                .Where(a => a.TransectionDate.Value.Year == currentYear && !a.IsDeleted 
                    && string.Equals(a.TransectionType, "Credited", StringComparison.CurrentCultureIgnoreCase)
                    && a.TransectionDate.Value.Month == monthNumber)
                .Sum(a => a.TransectionAmount) ?? 0;

            Income.Add(month, patientIncome + otherIncome);
        }
        return Income;
    }

    private Dictionary<string, decimal> GetOutcomePerMonthInCurrentYear(string[] monthName)
    {
        Dictionary<string, decimal> outcome = new Dictionary<string, decimal>();
        int currentYear = GetLocalDateTime().Year;

        foreach (var month in monthName)
        {
            int monthNumber = _monthNameToNumber[month];
            // 替换所有ToString("MMM")：通过月份数字匹配
            var expenseOut = _context.Expenses
                .Where(a => !a.IsDelete && a.ExpenseDate.Year == currentYear && a.ExpenseDate.Month == monthNumber)
                .Sum(a => a.ExpenseAmount) ?? 0;

            var stockOut = _context.StockTransectionTbs
                .Where(a => a.TransectionDate.Value.Year == currentYear && a.TransectionDate.Value.Month == monthNumber)
                .Sum(a => a.TransectionAmount) ?? 0;

            var refundOut = _context.PatientPaymentHistories
                .Where(a => a.PaymentDate.Year == currentYear && a.PaymentDate.Month == monthNumber && a.PaymentType == "Refund")
                .Sum(a => a.PaidAmount) ?? 0;

            var labOut = _context.labsPayments
                .Where(a => a.PaymentDate.Year == currentYear && a.PaymentDate.Month == monthNumber)
                .Sum(a => a.TotalPaid) ?? 0;

            var salaryOut = _context.EmployeeTransectionTbs
                .Where(a => !a.IsDeleted && a.TransectionDate.Value.Year == currentYear && a.TransectionDate.Value.Month == monthNumber)
                .Sum(a => a.TransectionAmount) ?? 0;

            var debitedOut = _context.OtherTransectionTbs
                .Where(a => !a.IsDeleted && a.TransectionType == "Debited" && a.TransectionDate.Value.Year == currentYear && a.TransectionDate.Value.Month == monthNumber)
                .Sum(a => a.TransectionAmount) ?? 0;

            outcome.Add(month, expenseOut + stockOut + refundOut + labOut + salaryOut + debitedOut);
        }
        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; }
}

关键修改说明 #

• 新增月份映射字典：将月份缩写转换为数字，避免字符串比较，确保EF Core能正确翻译查询
• 替换日期格式化逻辑：所有DateTime.ToString("MMM-yyyy")替换为Year和Month属性的直接比较，完全支持SQL翻译
• 优化状态统计：提前将分组后的预约状态统计为字典，减少重复遍历，提升性能
• 处理空值情况：在Sum结果后添加?? 0，避免无匹配数据时返回null导致视图报错

内容的提问来源于stack exchange，提问作者Hedayat Hoshmand