使用linqc#基于多個值進行左連接和計數

我有 2 個清單。串列 A 包含此值，

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

串列 B 包含此值，

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

我想做左連接（串列 A 中的所有值都必須在新串列中），并計算與狀態相關的任務數。我也想要級別值，因為稍后將在我的代碼中使用它。預期輸出：

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

我知道這里有很多答案提供了編碼的方法，但我仍然卡住了，因為我的代碼不起作用，它似乎沒有按方法分組，因為當我計數時，顯示的值是所有人的狀態。

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();

uj5u.com熱心網友回復：

問題在于，由于 的左連接語意DefaultIfEmpty()，您總是至少有一行。所以你需要添加一個謂詞到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();

或者，一種更簡單的方法是：不分組，而是使用另一個串列的相關計數

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();

我認為沒有理由在ToString這里使用，它可能會影響性能。Key并且Value應該分別在每個串列/表格上是相同的型別。

uj5u.com熱心網友回復：

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
}

標籤：C＃ sql 网 。网 林克

上一篇：通過忽略重復鍵創建字典

下一篇：使用私鑰將X509證書匯入certlm(.NETCore6)