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