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);
CodePudding user response：
Unfortunately it is unclear, what should happen, if a status occurs multiple times within one list, cause your aggregate can only hold one value per status.
One possibility to solve this issue would be:
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 allStates = first.Select(a => a.Status)
.Concat(second.Select(b => b.Status))
.Concat(third.Select(c => c.Status))
.Distinct();
var result = allStates
.Select(Status => new
{
Status,
ValueA = first.FirstOrDefault(a => a.Status == Status),
ValueB = second.FirstOrDefault(b => b.Status == Status),
ValueC = third.FirstOrDefault(c => c.Status == Status),
});
foreach (var item in result)
{
Console.WriteLine(item);
}
}
}
public record A(string Status, int ValueA);
public record B(string Status, int ValueB);
public record C(string Status, int ValueC);
Depending on the amount of items that have to be aggregated and the premise that each status occurs only once or never it could make sense to convert your lists to a Dictionary<string, A>, Dictionary<string, B>, etc. to improve the lookup and do something like this in the aggregate:
ValueA = dictFirst.ContainsKey(Status) ? dictFirst[Status] : null
For further improvements (this line makes the lookup twice) you could also factor out a method like this
private static T GetValueOrDefault<T>(IReadOnlyDictionary<string, T> dict, string status)
{
dict.TryGetValue(status, out T value);
return value;
}
And within the .Select() method call it with
ValueA = GetValueOrDefault(firstDict, Status);
Creating the dictionary for the list could be done with:
var firstDict = first.ToDictionary(a => a.Status);
CodePudding user response：
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
.Concat<object>(second)
.Concat<object>(third)
// group on Status value with help of switch expression
.GroupBy(el => el switch {
A a => new { status = a.Status },
B b => new { status = b.Status },
C c => new { status = c.Status },
},
// project groups with anonymous type
(key, group) => new {
status = key.status,
ValueA = group.Where(l => l is A).Cast<A>().FirstOrDefault()?.ValueA,
ValueB = group.Where(l => l is B).Cast<B>().FirstOrDefault()?.ValueB,
ValueC = group.Where(l => l is C).Cast<C>().FirstOrDefault()?.ValueC,
});
public record A(string Status, int ValueA);
public record B(string Status, int ValueB);
public record C(string Status, int ValueC);
CodePudding user response：
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();