I have 2 lists. List A consists of this value,

status | level
-----------------
open   | low
open   | medium
open   | high
closed | low
closed | medium
closed | high

List B consists of this value,

task | status | level
------------------------
A    | open   | low
B    | open   | medium
C    | closed | high
D    | closed | low
E    | open   | low

I want to do left join (all value inside list A must be in the new list), and count the number of tasks with related to status. I want the level value as well since it will be used later in my code. The expected output:

    status | level | count
    -------------------------
    open   | low      |  2
    open   | medium   |  1
    open   | high     |  0
    closed | low      |  1
    closed | medium   |  0
    closed | high     |  1

I know there are many answers here which provides the ways to code, but I'm still stuck, because my code doesnt work, it seems like it does not do the group by method because when I count, the value shown is one for all status.

var joined3 = (from id1 in joined
              join id2 in tr
              on new { lev = id1.Key.ToString(), stat = id1.Value.ToString() } equals new { lev = id2.Level.ToString(), stat = id2.Status.ToString() } into grouped
              from id2 in grouped.DefaultIfEmpty()
              group id2 by new {level = id1.Key, status = id1.Value } into grouped
              select new
              {
                    level = grouped.Key.level,
                    status = grouped.Key.status,
                    count =  grouped.Count()
              }).ToList();

CodePudding user response：

var list1 = new List<Type1>
{
    new Type1() {Status = StatusEnum.Open, Level = LevelEnum.Low},
    new Type1() {Status = StatusEnum.Open, Level = LevelEnum.Medium},
    new Type1() {Status = StatusEnum.Open, Level = LevelEnum.High},
    new Type1() {Status = StatusEnum.Closed, Level = LevelEnum.Low},
    new Type1() {Status = StatusEnum.Closed, Level = LevelEnum.Medium},
    new Type1() {Status = StatusEnum.Closed, Level = LevelEnum.High}
};

var list2 = new List<Type2>
{
    new Type2() {TaskDescription = "A", Status = StatusEnum.Open, Level = LevelEnum.Low},
    new Type2() {TaskDescription = "B", Status = StatusEnum.Open, Level = LevelEnum.Medium},
    new Type2() {TaskDescription = "C", Status = StatusEnum.Closed, Level = LevelEnum.High},
    new Type2() {TaskDescription = "D", Status = StatusEnum.Closed, Level = LevelEnum.Low},
    new Type2() {TaskDescription = "E", Status = StatusEnum.Open, Level = LevelEnum.Low}
};

var list3 = new List<Type3>();
foreach (var t in list1)
{
    list3.Add(new Type3()
        {Level = t.Level, Status = t.Status, Count = list2.Count(x => x.Level == t.Level && x.Status == t.Status)});
}

foreach (var t in list3)
{
    Console.WriteLine($"{t.Status}/{t.Level}/{t.Count}");
}


class Type1
{
    public StatusEnum Status { get; set; }
    public LevelEnum Level { get; set; }
}

class Type2 : Type1
{
    public string TaskDescription { get; set; }
}

class Type3 : Type2
{
    public int Count { get; set; }
}

public enum StatusEnum
{
    Open,
    Closed
}

public enum LevelEnum
{
    Low,
    Medium,
    High
}

CodePudding user response：

The problem is that because of the left-join semantics of DefaultIfEmpty(), you always have at least one row. So you need to add a predicate to the Count()

var joined3 = (
    from id1 in joined
    join id2 in tr
       on new { lev = id1.Key, stat = id1.Value } equals new { lev = id2.Level, stat = id2.Status } into grouped
    from id2 in grouped.DefaultIfEmpty()
    group id2 by new {level = id1.Key, status = id1.Value } into grouped
    select new
    {
        level = grouped.Key.level,
        status = grouped.Key.status,
        count =  grouped.Count(id2 => id2.Key != null)
    }).ToList();

Alternatively, a simpler method is: don't group, but instead use a correlated count of the other list

var joined3 = (
    from id1 in joined
    select new
    {
        level = id1.level,
        status = id1.status,
        count =  tr.Count(id2 => id2.Key == id1.Key && id2.Value == id1.Value)
    }).ToList();

I see no reason to use ToString here, and it is likely to impact performance. Key and Value should be the same type on each list/table respectively.