using (var unitOfWork = UnitOfWorkFactory.Create()) { var repository = GetRepository<DmEmployeeGroup>(unitOfWork); var employeeGroups = repository.AsQueryable(); var linkedRepository = GetRepository<DmEmployeeGroupEmployee>(unitOfWork); var groupEmployee = linkedRepository.AsQueryable(); var joined = await employeeGroups .Join(groupEmployee, (DmEmployeeGroup a) => a.Id, (DmEmployeeGroupEmployee b) => b.EmployeeGroupId, (a, b) => new { a, b }) .OrderBy(a => a.a.Id) .ToListAsync(); var result = joined.GroupBy(x => x.a.Id) .Select(x => { var group = x.First().a; group.Employees = x.Select(y => y.b); return group; }); return result.Select(so => so.ToDomain()); }
Left join version:
using (var unitOfWork = UnitOfWorkFactory.Create()) { var repository = GetRepository<DmEmployeeGroup>(unitOfWork); var employeeGroups = repository.AsQueryable(); var linkedRepository = GetRepository<DmEmployeeGroupEmployee>(unitOfWork); var groupEmployee = linkedRepository.AsQueryable(); var joined = await employeeGroups .GroupJoin(groupEmployee.DefaultIfEmpty(), (DmEmployeeGroup a) => a.Id, (DmEmployeeGroupEmployee b) => b.EmployeeGroupId, (a, b) => new { a, b }) .ToListAsync(); var result = joined .Select(x => { x.a.Employees = x.b.ToList(); return x.a; }); return result.Select(so => so.ToDomain()); }
255400cookie-checkC# Linq: join & group two queryables