Show grouped data in view

Question

have the models department and institution, how do you see yourself next.

public class Institution
{
   public long? InstitutionId { get; set; }
   public string Name { get; set; }
   public string Address { get; set; }
   public virtual ICollection<Department> Departments { get; set; }
}

public class Department
{
   public long? DepartmentId { get; set; }
   public string Name { get; set; }
   public long? InstitutionId { get; set; }
   public Institution Institution { get; set; }
}

I’m trying to show in a view the departments grouped by institution. Here’s my controller and my service:

//controller
public async Task<IActionResult> DepartmentGroupedByInstitution()
{
   return View(await _departmentService.GetDepartmentGroupedByInstitution());
}

//service
public async Task<List<IGrouping<Institution, Department>>> GetDepartmentGroupedByInstitution()
{
   var result = await _context.Departments.Include(d => d.Institution).OrderBy(d => d.InstitutionId).GroupBy(d => d.Institution).ToListAsync();            
   return result;
}

However, the following error occurs:

InvalidOperationException: The LINQ expression 'DbSet<Department>()
.OrderBy(d => d.InstitutionId)
.Join(
inner: DbSet<Institution>(),
outerKeySelector: d => EF.Property<Nullable<long>>(d, "InstitutionId"),
innerKeySelector: i => EF.Property<Nullable<long>>(i, "InstitutionId"),
resultSelector: (o, i) => new TransparentIdentifier<Department, Institution>(
Outer = o,
Inner = i
))
.GroupBy(
keySelector: d => d.Inner,
elementSelector: d => d.Outer)' 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.

Would someone please help? Thank you in advance.

Answer 1

This error probably happens because of the element call sequence in the query.

Try to change that:

var result = await _context.Departments.Include(d => d.Institution).OrderBy(d => d.InstitutionId).**GroupBy**(d => d.Institution).ToListAsync();

That’s why:

var result = await _context.Departments.Include(d => d.Institution).GroupBy(d => d.Institution).OrderBy(d => d.InstitutionId).ToListAsync();