Linq outer joins over many to many table

Question

I've got the following structure

Teams >- Membership -< Employees

Membership is a many to many relation table with extra fields StartDate and EndDate, which is set up per this example: Create code first, many to many, with additional fields in association table.

What I'm trying to accomplish is to get in one Linq query the all the Teams (left outer join), and Employees (left/right outer join), basically resulting in a big list with teams (and coupled employees if any) and employees (with coupled teams if any).

Is this possible?

I'm trying to do it via the following but not getting there completely:

var queryable = (from t in Context.Teams
                    join m in Context.Membership on t.Id equals m.TeamId into tm
                    from result in tm.DefaultIfEmpty()
                    join e in Context.Employees on result.EmployeeId equals e.Id into em
                    from r in em.DefaultIfEmpty()
                    select new { TeamLinq = t, MembershipLinq = result }).ToList();

or

var q = (from m in Context.Membership
            join t in Context.Teams on m.TeamId equals t.Id into tm
            join e in Context.Employees on m.EmployeeId equals e.Id into em
            from result in tm.DefaultIfEmpty()
            from result2 in em.DefaultIfEmpty()
            select new { Foo = result, Bar = result2 }).ToList();

but not getting what I'm after.

In simple SQL I would write it like

select * from Teams t left outer join Membership m on t.Id = m.TeamId
right outer join Employees e on e.Id = m.EmployeeId

sounds to me that you want to have left join teams-membership and then another left join to Employees. So, in the the first example you would do `join e in Context.Employees on m.EmployeeId equals e.Id into em`. Basically, instead of `result.EmployeeId` put `m.EmployeeId` — T.S., Oct 25 '17 at 15:47

score 0 · Answer 1 · answered Oct 25 '17 at 16:32

This should work

var queryable = 
   (from t in Context.Teams
    from m in Context.Membership.Where(r => t.Id == r.TeamId).DefaultIfEmpty()
    from e in Context.Employees.Where(r => m != null && m.EmployeeId == r.Id).DefaultIfEmpty()
    select new { Team = t, Membership = m }).ToList();

It works in Linq-to-objects.

    var list = (from t in teams
                from m in mtm.Where(r => t.Id == r.TeamId).DefaultIfEmpty()
                from e in emps.Where(r => m != null && m.EmployeeId == r.Id).DefaultIfEmpty()
                select new { t = t.Id, e = e != null ? e.Id : -1 }).ToList();

    list.ForEach(x => Console.WriteLine("Team: {0}; Emp: {1}", x.t, x.e)    )   ;

Team: 1; Emp: -1
Team: 2; Emp: -1
Team: 3; Emp: -1
Team: 4; Emp: 2
Team: 4; Emp: 3
Team: 4; Emp: 4
Team: 5; Emp: 2
Team: 5; Emp: 4
Team: 6; Emp: 2
Team: 7; Emp: 2
Team: 8; Emp: -1
Team: 9; Emp: -1
Team: 10; Emp: -1
Team: 11; Emp: -1
Team: 12; Emp: -1

Didn't give all the employees which are not added to a team. I updated my original question. — Kris van der Mast, Oct 26 '17 at 07:15
@XIII That is correct. But if you return all emps, you will lose teams. sounds like you need some different join. would be more useful, if you posted actual data sample, the way you want end result to look — T.S., Oct 26 '17 at 14:10

Linq outer joins over many to many table

1 Answers1