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();
ViewBag.CheckupPatients = _context.PatientTbs.Where(a => a.PatientStatus == "Checkup" && !a.IsDeleted && a.RegisterDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Count();
ViewBag.WorkingPatients = _context.PatientTbs.Where(a => a.PatientStatus == "Working" && !a.IsDeleted && a.RegisterDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Count();
ViewBag.ClosedPatients = _context.PatientTbs.Where(a => a.PatientStatus == "Closed" && !a.IsDeleted && a.RegisterDate.Value.Date.ToString("MMM-yyyy").Equals(date.Value.ToString("MMM-yyyy"))).Count();
ViewBag.NumberOfPatientRegisterd = _context.PatientTbs.Where(a => !a.IsDeleted && a.IsActive.Value).Count();
ViewBag.NumberOfPatientRegisterdToday = _context.PatientTbs.Where(a => !a.IsDeleted && a.IsActive.Value && a.RegisterDate == GetLocalDateTime().Date).Count();
ViewBag.DailyPatientIcomeTreatmentFee = _context.PatientPaymentHistories.Where(a => a.PaymentDate.ToString("MMM-dd-yyyy").Equals(GetLocalDateTime().ToString("MMM-dd-yyyy")) && a.PaymentType == "Treatment Fee").Sum(a => a.PaidAmount);
ViewBag.DailyPatientIcomeVisitFee = _context.PatientPaymentHistories.Where(a => a.PaymentDate.ToString("MMM-dd-yyyy").Equals(GetLocalDateTime().ToString("MMM-dd-yyyy")) && a.PaymentType == "Checkup Fee").Sum(a => a.PaidAmount);
ViewBag.DailyClinicOut = _context.Expenses.Where(a => !a.IsDelete && a.ExpenseDate.Date == GetLocalDateTime().Date).Sum(a => a.ExpenseAmount)
_context.StockTransectionTbs.Where(a => a.TransectionDate.Value.Date == GetLocalDateTime().Date).Sum(a => a.TransectionAmount)
_context.PatientPaymentHistories.Where(a => a.PaymentDate.Date == GetLocalDateTime().Date && a.PaymentType == "Refund").Sum(a => a.PaidAmount)
_context.labsPayments.Where(a => a.PaymentDate.Date == GetLocalDateTime().Date).Sum(a => a.TotalPaid)
_context.EmployeeTransectionTbs.Where(a => !a.IsDeleted && a.TransectionDate.Value.Date == GetLocalDateTime().Date).Sum(a => a.TransectionAmount)
_context.OtherTransectionTbs.Where(a => !a.IsDeleted && a.TransectionType == "Debited" && a.TransectionDate.Value.Date == GetLocalDateTime().Date).Sum(a => a.TransectionAmount);
ViewBag.TotalMainAccountAmount = _context.AccountTbs.FirstOrDefault().Amount;
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; }
}
CodePudding user response：
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).
CodePudding user response：
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:
public static class QueryableExtensions
{
public static IQueryable<T> FilterByMonth<T>(this IQueryable<T> query, DateTime date, Expression<Func<T, DateTime?>> dateField)
{
var startMonth = new DateTime(date.Year, date.Month, 1);
var nextMonth = startMonth.AddMonths(1);
var entityParam = dateField.Parameters[0];
var fieldExpr = dateField.Body;
// e.DateField >= startMonth && e.DateField < nextMonth
var filterExpression = Expression.AndAlso(
Expression.GreaterThanOrEqual(fieldExpr, Expression.Constant(startMonth, fieldExpr.Type)),
Expression.LessThan(fieldExpr, Expression.Constant(nextMonth, fieldExpr.Type)));
// e => e.DateField >= startMonth && e.DateField < nextMonth
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;