How to perform right join outer join using linq

Question

I have two sets of data: trainedOfficers and officersToTrain. These data sets share a ClubId in common. I'm trying to make sure that trainedOfficers don't appear in officersToTrain so I want to perform right outer join. Looking at this image:

• A = trainedOfficers
• B = officersToTrain

I have tried doing the following query but it yields no results.

public void SetTrainedClubOfficers(ILookup<ClubID, ClubOfficerAuthority> clubsAuthorityLookup)
{
    var clubIds = clubsAuthorityLookup.Select(x => x.Key);
    var trainedOfficers = GetTrainedClubOfficers(clubIds.ToArray());
    var clubsToTrain = from trainedOfficer in trainedOfficers
                       join officer in clubsAuthorityLookup
                       on trainedOfficer.Key equals officer.Key into joined
                       from officer in joined.DefaultIfEmpty()
                       select new
                       {
                           ClubId = officer.Key,
                           Officers = officer.Select(club => club)
                       };
}

How can I get the right outer join?

Answer 1

    public void SetTrainedClubOfficers(ILookup<ClubID, ClubOfficerAuthority> clubsAuthorityLookup)
        {
            var clubIds = clubsAuthorityLookup.Select(x => x.Key);
            var trainedOfficers = GetTrainedClubOfficers(clubIds.ToArray());
            var clubsToTrain = from trainedOfficer in trainedOfficers
                               join officer in clubsAuthorityLookup
                               on trainedOfficer.Key equals officer.Key into joined
                               from j in joined.DefaultIfEmpty()
                               select new
                               {
                                   ClubId = j.Key,
                                   Officers = officer.Select(club => club)
                               };
}

This is not tested but I think it should work. You were using officer more than once.

Edit: Not sure why you would need two tables for this though. You could just a have field in the Officers table that flags 'IsTrained.' Then for displaying who is trained vs not, you could just key off that one field.