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

Solution 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);

Solution 2

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
    };

Solution 3

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
            };

Comments

[Sam Nelson]

Not certain, but it may be more efficient to perform the inner join AFTER the where clause, so that it doesn't bother joining on the records filtered out by your left join. You can have multiple where clauses (so, GroupJoin(training), Where(Training == null), Join(manager), Where(Manager.Id == managerId). I suppose it would depend on which LINQ provider you're using...

[Rob C]

This lambda expression is returning the results I'm expecting - perfect! I'll take a look at the efficiency of the query as per you comment. Thanks a lot! :)

[Travis Peterson]

How would you do this having the gj => gj.Staff.manager_id changed to use Training instead? like this gj => gj.Training.???

[Sam Nelson]

Yes, the type of the gj variable is defined in the 4th parameter of the GroupJoin, it's an anonymous type with members Staff and Training. Training may have it's default value if there was no matching staff_id value for id, which, if it is a reference type, should be null. Assuming that, you could inner join the manager collection to Training.staff_id instead using the C#6 null conditional operator - change the second parameter of the Join call to: gj => gj.Training?.staff_id