C# Linq: join & group two queryables

Date: 2019-09-03
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());
}
25540cookie-checkC# Linq: join & group two queryables