SQL:Linq-列值发生变化的不同行

Question

如何在LINQ中进行筛选，在LINQ，如果查询的Level列上升了一列，则查询只对随后的两行进行分组。

这就是我此刻所拥有的：

var alert = db.Logs.OrderBy(u => u.Time).GroupBy(r => r.EquipmentNr).Where(s => s.Count() > 1);

但是，它会获取所有未找到的行，这些行在"级别"列中是不同的，并且它们必须随着时间的推移而不同。必须按时间顺序并排排列。

或者我应该创建一个分组类，然后手动运行我已经拥有的查询，并添加是否找到它们？

public class Log
{
    public int Id { get; set; }
    public DateTime Time { get; set; }
    public Shift Shift { get; set; }
    public int EquipmentNr { get; set; }
    public int OrderNr { get; set; }
    public bool SupervisorCalled { get; set; }
    public string Issue { get; set; }
    public string Repairs { get; set; }
    public string Responsible { get; set; }
    public Level Level { get; set; }
}

Answer 1

大多数时候，对于列表中元素以某种方式相关的顺序处理，经典的foreach比LINQ要好得多。通常情况下，LINQ甚至无法做到这一点。然而，在这种情况下，只比较连续的元素，因此可以使用Aggregate。

分组条件为：level(n) - level(n-1) = 1。因此，我们应该为列表中满足此条件的每个元素添加相同的分组键。这里有一种方法（使用Linqpad）。每个元素存储在Tuple中，其中Item1是分组密钥。然后元组按以下键分组：

void Main()
{
    var list = new List<Equipment>
    { 
    new Equipment{Level = 11},
    new Equipment{Level = 1},
    new Equipment{Level = 18},
    new Equipment{Level = 0},
    new Equipment{Level = 6},
    new Equipment{Level = 4},
    new Equipment{Level = 5},
    new Equipment{Level = 20},
    new Equipment{Level = 9},
    new Equipment{Level = 14},
    new Equipment{Level = 12},
    new Equipment{Level = 17},
    new Equipment{Level = 2},
    new Equipment{Level = 13},
    new Equipment{Level = 15},
    };
    list.OrderBy(eq => eq.Level)
        .Aggregate
        (
            new List<Tuple<int,Equipment>>(), // Seed the list of tuples
            (tuples,eq) =>
            {
                // The grouping condition: compare to the previous item
                // (if present) and use its Level as grouping key if the
                // condition is met.
                var key = tuples.Any() 
                            && eq.Level - tuples.Last().Item2.Level == 1
                        ? tuples.Last().Item1
                        : eq.Level; 
                tuples.Add(Tuple.Create(key, eq)); 
                return tuples;
            }, 
            tuples => tuples.GroupBy (tuple => tuple.Item1)
        // Done! Now just a Select for display
        ).Select
        (tupleGroup =>
            new { 
                    tupleGroup.Key,
                    Numbers = string.Join(",", tupleGroup
                                               .Select(e => e.Item2.Level))
                }
        ). Dump();
}
// Define other methods and classes here
class Equipment
{
    public int Level { get; set; }
}

输出：

Key  Numbers 
---  --------------
0    0,1,2 
4    4,5,6 
9    9 
11   11,12,13,14,15 
17   17,18 
20   20