Join multiple lists of objects in c#

Question

I have three lists that contain objects with following structure:

List1

 - Status
 - ValueA

List2

- Status
- ValueB

List3

- Status
- ValueC

I want to joint the lists by status to get a final list that contains object with following structure:

- Status
- ValueA
- ValueB
- ValueC

Not every list has all the status. So a simple (left) join won't do it. Any ideas how to achieve the desired result? I tried with

var result = from first in list1
    join second in list2 on first.Status equals second.Status into tmp1
    from second in tmp1.DefaultIfEmpty()
    join third in list3 on first.Status equals third.Status into tmp2
    from third in tmp2.DefaultIfEmpty()
    select new { ... };

But result is missing a status. Here is a full MRE:

using System;
using System.Linq;
using System.Collections.Generic;

public class Program
{
    public static void Main()
    {
        List<A> first = new List<A>() { new A("FOO", 1), new A("BAR", 2) };
        List<B> second = new List<B>() { new B("FOO", 6), new B("BAR", 3) };
        List<C> third = new List<C>() { new C("BAZ", 5) };
        
        var result = from f in first
            join s in second on f.Status equals s.Status into tmp1
            from s in tmp1.DefaultIfEmpty()
            join t in third on f.Status equals t.Status into tmp2
            from t in tmp2.DefaultIfEmpty()
            select new 
            {
                Status = f.Status,
                ValueA = f.ValueA,
                ValueB = s.ValueB,
                ValueC = t.ValueC,
            };

    }
}

public record A(string Status, int ValueA);
public record B(string Status, int ValueB);
public record C(string Status, int ValueC);

Answer 1

With assumption that status names are unique per list here is a solution in a single query with help of switch expressions (available since C# 8.0):

using System;
using System.Linq;
using System.Collections.Generic;

List<A> first = new List<A>() { new A("FOO", 1), new A("BAR", 2) };
List<B> second = new List<B>() { new B("FOO", 6), new B("BAR", 3) };
List<C> third = new List<C>() { new C("BAZ", 5) };

var result = first
    // concat lists together
    .Cast<object>()
    .Concat(second)
    .Concat(third)
    // group on Status value with help of switch expression
    .GroupBy(el => el switch {
        A a => a.Status,
        B b => b.Status,
        C c => c.Status,
    },
    // project groups with anonymous type
    (Status, group) => new { 
        Status,
        ValueA = group.OfType<A>().Select(a => a.ValueA).Cast<int?>().FirstOrDefault(),
        ValueB = group.OfType<B>().Select(b => b.ValueB).Cast<int?>().FirstOrDefault(),
        ValueC = group.OfType<C>().Select(c => c.ValueC).Cast<int?>().FirstOrDefault()
    }); 

public record A(string Status, int ValueA);
public record B(string Status, int ValueB);
public record C(string Status, int ValueC);

Answer 2

This can't using left join.First you must get all keies,then using all keies left join other lists:

var keys = first.Select(item => item.Status).ToList();
keys.AddRange(second.Select(item => item.Status));
keys.AddRange(third.Select(item => item.Status));

keys = keys.Distinct().ToList();

var result = (from k in keys JOIN
       f in first on k equals f.Status into tmp0
       from f in tmp0.DefaultIfEmpty()
       join s in second on k equals s.Status into tmp1
       from s in tmp1.DefaultIfEmpty()
       join t in third on k equals t.Status into tmp2
       from t in tmp2.DefaultIfEmpty()
       select new {
             Status = k,
             ValueA = f?.ValueA,
             ValueB = s?.ValueB,
             ValueC = t?.ValueC,
       }
).ToList();