C# LINQ threw this error. how can I fix it?-CodePudding

Code throws this exception - how can I solve it?

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression: KeySelector: (Nullable)d.DiseasesCategoryId, ElementSelector:EntityShaperExpression: EntityType: Diagnosis ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .GroupBy(p => p.Patient.Gender)' could not be translated. 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Code:

IQueryable<Diagnosis> diagnoses = _context.Diagnosis
    .Include(d => d.Patient)
    .Include(d => d.Prescription);
IQueryable<Diagnosis> diagnosesWithSpecification = diagnoses
    .Where(d => d.Prescription.UpdatedOn.Year == year && 
                d.Prescription.UpdatedOn.Month == month && 
                d.Prescription.HospitalId == hospitalId);

int currentYear = DateTime.Now.Date.Year;
var groupByDiseases = await diagnosesWithSpecification
    .GroupBy(dCat => dCat.DiseasesCategoryId)
    .Select(x => new
    {
       DiseasesCategoryId = x.Key,
       Gender = x.GroupBy(p => p.Patient.Gender).Select(y => new
       {
           Gender = y.Key,
           ZeroToFive = y.Count(p => (currentYear - p.Patient.DoB.Value.Date.Year) >= 0 && (currentYear - p.Patient.DoB.Value.Date.Year) <= 5),
           SixToFifteen = y.Count(p => (currentYear - p.Patient.DoB.Value.Date.Year) >= 6 && (currentYear - p.Patient.DoB.Value.Date.Year) <= 15),
           SixteenToThirty = y.Count(p => (currentYear - p.Patient.DoB.Value.Date.Year) >= 16 && (currentYear - p.Patient.DoB.Value.Date.Year) <= 30),
           ThirtyOneToFourtyFive = y.Count(p => (currentYear - p.Patient.DoB.Value.Date.Year) >= 31 && (currentYear - p.Patient.DoB.Value.Date.Year) <= 45),
           FourtySixToSixty = y.Count(p => (currentYear - p.Patient.DoB.Value.Date.Year) >= 46 && (currentYear - p.Patient.DoB.Value.Date.Year) <= 60),
           SixtyOnePlus = y.Count(p => (currentYear - p.Patient.DoB.Value.Date.Year) > 60),
           Total = y.Count()
       }).ToList(),
       TotalPatient = x.Count()
    }).ToListAsync();

CodePudding user response：

Use .ToList() before call none databse functions.

diagnosesWithSpecification.ToList().GroupBy(dCat => dCat.DiseasesCategoryId)...

CodePudding user response：

You need grouping on client side. Before that it is needed to simplify returned result set.

var data = await _context.Diagnosis
    .Where(d => d.Prescription.UpdatedOn.Year == year && 
                d.Prescription.UpdatedOn.Month == month && 
                d.Prescription.HospitalId == hospitalId)
    .Select(d = new 
    { 
        d.DiseasesCategoryId,
        d.Patient.Gender,
        p.Patient.DoB
     })
     .ToListAsync();

int currentYear = DateTime.Now.Date.Year;

var groupByDiseases = data
    .GroupBy(dCat => dCat.DiseasesCategoryId)
    .Select(x => new
    {
       DiseasesCategoryId = x.Key,
       Gender = x.GroupBy(p => p.Gender).Select(y => new
       {
           Gender = y.Key,
           ZeroToFive = y.Count(p => (currentYear - p.DoB.Value.Date.Year) >= 0 && (currentYear - p.DoB.Value.Date.Year) <= 5),
           SixToFifteen = y.Count(p => (currentYear - p.DoB.Value.Date.Year) >= 6 && (currentYear - p.DoB.Value.Date.Year) <= 15),
           SixteenToThirty = y.Count(p => (currentYear - p.DoB.Value.Date.Year) >= 16 && (currentYear - p.DoB.Value.Date.Year) <= 30),
           ThirtyOneToFourtyFive = y.Count(p => (currentYear - p.DoB.Value.Date.Year) >= 31 && (currentYear - p.DoB.Value.Date.Year) <= 45),
           FourtySixToSixty = y.Count(p => (currentYear - p.DoB.Value.Date.Year) >= 46 && (currentYear - p.DoB.Value.Date.Year) <= 60),
           SixtyOnePlus = y.Count(p => (currentYear - p.DoB.Value.Date.Year) > 60),
           Total = y.Count()
       }).ToList(),
       TotalPatient = x.Count()
    }).ToList();