LINQ Lambda Left join with an Inner join

Question

I have written a LINQ lambda query which so far which returns all staff which do not have an associated training row which works fine. I now need to amend my where clause to use the manager id joining manager table onto staff.

I am a little unsure how to modify this left join lambda to include an inner join. If anyone can point me in the right direction that would be very much appreciated.

    var managerId = 1;

    var query = db.staff

                .GroupJoin(db.training,
                    s => s.id,

                    t => t.staff_id,
                    (s, t) => new {Staff = s, Training = t.FirstOrDefault()})

    //TODO: join manager.id on staff.manager_id


            .Where(st => st.Training==null);//TODO: modify where clause && manager.id == managerId 

Thanks

Answer 1

Inner joins are performed with the Join method. I think your query should go something like this:

var query = db.staff
              .GroupJoin(db.training,
                         s => s.id,
                         t => t.staff_id,
                         (s, t) => new { Staff = s, Training = t.FirstOrDefault() })
              .Join(db.manager,
                    gj => gj.Staff.manager_id,
                    m => m.id,
                    (gj, m) => new { Staff = gj.Staff, Training = gj.Training, Manager = m })
              .Where(st => st.Training == null
                        && st.Manager.id == managerId);

Answer 2

You can do the following (I've not used method chaining syntax to make it more readable IMO):

var query = from s in db.staff
            join m in db.manager on s.manager_id equals m.id
            join t in db.training on s.id equals t.staff_id into tr
            from training in tr.DefaultIfEmpty()
            select new
            {
                Staff = s,
                Training = training
            };

Answer 3

Like this maybe:

var query = from s in db.staff
    join m in db.manager on s.manager_id equals m.id
    from t in db.training
        .Where(w=>w.staff_id==s.id).DefaultIfEmpty()
    select new
    {
        Staff = s,
        Training = training
    };