Losing records when using Join with LINQ

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.

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!