Losing records when using Join with LINQ

Question

Using C# and LINQ, I am trying to join lists gained from searching two seperate tables. Let's say I have two tables that I am pulling different details for different items.

var _listOne = await _tableOne.Select( x => new
{
   Name = x.Name,
   ID = x.ID,
}).ToArrayAsync();

var _listTwo = await _tableTwo.Select( x => new
{
   Name = x.Name,
   Leader = x.Leader,
   Score = x.Score
}).ToArrayAsync();

Note this is generalized information, I have much more fields I grab and a lot of filters for each set of the list pertaining to certain conditions.

When I try and join these two lists, I lose a few records and I think it's because of how the mapping works with .Join().

So, it's setup like:

var _joinedLists = _listOne.Join(_listTwo,
                                 item => item.Name,
                                 details => details.Name,
                                 (item, details) => new
                                 {
                                   itemName = item.Name,
                                   ID = item.ID,
                                   leaderName = details.Leader,
                                   score = details.Score
                                 }).OrderByDescending(x => x.ID).ToArray();

Let's say some record named "A" is in _tableOne, but "A" is not in _tableTwo, it does not join "A" in the list of _joinedLists, it leaves it out.

The main goal is I want to keep all records of _listOne even if some related record is not in _listTwo in my _joinedLists.

Is this how .Join() is suppose to work, and is there a way around this? I've looked at Union, but it seems to do that you have to have the same fields (or I suppose return types) for each of them and for my purpose that will not work. Tried googling around for this issue, but could not find anything other than Union/Concat (but like I said it does not work for my purpose).

Thanks.

There's are three unions... In terms of SQL(or sets theory) ... Which you wana achieve? There is left outer join which only apply left values and correspond right there is right outer join which is opposite... And there is full outer join which apply to both and where is no corresponding values on the opposite side it fills with nulls — Selvin, Aug 03 '23 at 21:50
@Selvin Let's say the main focus is the leader pertaining to each record from tableOne, but maybe every record doesn't have a leader (or possibly just left off tableTwo) and I want to make sure I keep all of the records from tableOne in my list. Would I use the left outer join? — CodedRoses, Aug 03 '23 at 21:51
What is connection between one and two(list or as we say in math set) ? One to one or one to many? Anyway if you wana all set from one and corresponding from two you use left outer... If you wana only one which HAS corresponding values in two you use inner join (common part) - this exclude one which has no corresponding two — Selvin, Aug 03 '23 at 21:54
@Selvin In this case, I would say One to One. As there will only be one name relating to _tableOne in _tableTwo. (Please bear with me as relational databases are new to me). — CodedRoses, Aug 03 '23 at 21:56
[Left join in linq docs](https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins) — stuartd, Aug 03 '23 at 21:58

score 1 · Accepted Answer · answered Aug 04 '23 at 13:25

What you are looking for is a LEFT JOIN, get all the left source data and join it to other source data regardless if they match or not. If there is no join link (as you said), the RIGHT source data fields will be NULL.

The left join for C# is the method GroupJoin and I'll show you two ways to do it.

First option

The first one is following what you already did, but the problem with this approach is that you fetch all the data from tableOne into memory, store this data in an array, then get all the data from tableTwo and save it in memory as well and the third array is the join of this data. As you can see, we have 2 collections that can have thousands of data and are not used for anything. So I already say that the second option is the best.

var listOne = await _tableOne.Select( x => new
{
   Name = x.Name,
   ID = x.ID,
}).ToArrayAsync();

var listTwo = await _tableTwo.Select( x => new
{
   Name = x.Name,
   Leader = x.Leader,
   Score = x.Score
}).ToArrayAsync();


var joinedLists = listOne.GroupJoin(listTwo,
                                    item => item.Name,
                                    details => details.Name,
                                    (item, details) => new
                                    {
                                        ItemName = item.Name,
                                        item.ID,
                                        LeaderName = details.DefaultIfEmpty().FirstOrDefault()?.Leader,
                                        Score = details.DefaultIfEmpty().FirstOrDefault()?.Score
                                    })
                          .OrderByDescending(x => x.ID)
                          .ToArray();

Second option

As I mentioned before, I think this second option is better, since we fetch from the database only a single list with the join already done. So we save memory and less processing.

var joinedLists = _tableOne.GroupJoin(_tableTwo,
                                      item => item.Name,
                                      details => details.Name,
                                      (item, details) => new
                                      {
                                          ItemName = item.Name,
                                          ID = item.ID,
                                          LeaderName = details.DefaultIfEmpty().FirstOrDefault()?.Leader,
                                          Score = details.DefaultIfEmpty().FirstOrDefault()?.Score
                                      })
                            .OrderByDescending(x => x.ID)
                            .ToArray();

By the way, I changed the name of your variables. By convention (Microsoft), do not use underscore for method scope variables.

Keep coding!

Thanks! This is extremely helpful, and yeah I was trying to find ways to make the code more efficient, this is perfect. — CodedRoses, Aug 07 '23 at 11:54

Losing records when using Join with LINQ

1 Answers1