Linq - 如何将扁平数据转换为分层结构？

Question

我有一个由Linq查询生成的结果集，我想将其转换为层次结构的结果集。前两列将表示“主”行，第3和第4列将表示主行的子列表，第5和第6列将表示主行的第二个子列表。包含值1971的列是连接列。

最终结果应该是一个主对象，带有容器（G2列）和打印机（G3列）的列表。

如何编写查询以将其转换为分层形式？

G1_ID   G1_CellName  G2_ContainerID G2_ID   G2_SerialNumber G3_ID      G3_PrinterName
1971    Default Cell    1935           1971    1101929         1971       PBG-PrtEmulator1
1971    Default Cell    1936           1971    1101930         1971       PBG-PrtEmulator1
1971    Default Cell    2189           1971    1102183         1971       PBG-PrtEmulator1

- Randy Minder

---

只是为了澄清一下...您想要一个层次结构，看起来像是对象 - 单元格 > 带有Cell.Containers、Cell.SerialNumbers、Cell.PrinterNames（作为集合）吗？ - Tom

这是Linq to Sql，Linq to EF，Linq to objects还是其他什么东西？ - Doctor Jones

@Tom - 我想要一个层次结构，看起来像是Cell -> Cell.Containers和Cell -> PrinterNames。序列号列是Container的属性。 - Randy Minder

@DoctaJonez - 直接使用Linq。此查询将在执行L2S查询后在客户端上执行。我将把结果转换为IEnumerable，然后创建分层结果。 - Randy Minder

Answer 1

groupby?

var result = from eachData in data
group eachData by new{ eachData .G1_ID, eachData .G1_CellName }
into g1
from eachG1 in g1
group eachG1 by new { eachG1.G2_..., eachG1.G2_... }
into g2
for eachG2 in g2
group eachG2 by new { eachG2.G3_... }
into g3
select g3;

我还没有测试过它，但我确定它看起来会像这样。

- Bonshington

---

我曾考虑过使用GroupBy，但那样无法将g2和g3数据的集合分组到每个g1元素中。我认为你必须对原始列表进行三次遍历，但也许我错了 :) - Tom

Answer 2

好的，这是一个相当发人深省的问题。我过去做了很多数据展平的工作，通常我会使用字典来保存所有唯一的值，然后在之后将它们匹配起来。

你要求使用LINQ，现在我想不出一种单次通过的方法来做到这一点，所以我有了这个VB代码...

Private Class FlatObj
    Public Property G1_ID As Integer
    Public Property G1_CellName As String
    Public Property G2_ContainerID As Integer
    Public Property G2_ID As Integer
    Public Property G2_SerialNumber As Integer
    Public Property G3_ID As Integer
    Public Property G3_PrinterName As String
End Class

Private Class G1
    Public Property ID As Integer
    Public Property CellName As String
    Public Property Containers As New List(Of G2)()
    Public Property PrinterNames As New List(Of G3)()
    Public Overrides Function Equals(ByVal obj As Object) As Boolean
        Return ID.Equals(CType(obj, G1).ID)
    End Function
    Public Overrides Function GetHashCode() As Integer
        Return ID.GetHashCode()
    End Function
End Class

Private Class G2
    Public Property fID As Integer
    Public Property ContainerID As Integer
    Public Property SerialNumber As Integer
    Public Overrides Function Equals(ByVal obj As Object) As Boolean
        Return ContainerID.Equals(CType(obj, G2).ContainerID)
    End Function
    Public Overrides Function GetHashCode() As Integer
        Return ContainerID.GetHashCode()
    End Function
End Class

Private Class G3
    Public Property fID As Integer
    Public Property PrinterName As String
    Public Overrides Function Equals(ByVal obj As Object) As Boolean
        Return PrinterName.Equals(CType(obj, G3).PrinterName)
    End Function
    Public Overrides Function GetHashCode() As Integer
        Return PrinterName.GetHashCode()
    End Function
End Class

Dim fromDb As New List(Of FlatObj) From
    {
        New FlatObj() With {.G1_ID = 1971, .G1_CellName = "Default Cell", .G2_ContainerID = 1935, .G2_ID = 1971, .G2_SerialNumber = 1101929, .G3_ID = 1971, .G3_PrinterName = "PBG-PrtEmulator1"},
        New FlatObj() With {.G1_ID = 1971, .G1_CellName = "Default Cell", .G2_ContainerID = 1936, .G2_ID = 1971, .G2_SerialNumber = 1101930, .G3_ID = 1971, .G3_PrinterName = "PBG-PrtEmulator1"},
        New FlatObj() With {.G1_ID = 1971, .G1_CellName = "Default Cell", .G2_ContainerID = 2189, .G2_ID = 1971, .G2_SerialNumber = 1102183, .G3_ID = 1971, .G3_PrinterName = "PBG-PrtEmulator1"}
    }

Dim g1s = fromDb.Select(Function(x) New G1 With
                                    {
                                        .ID = x.G1_ID,
                                        .CellName = x.G1_CellName
                                    }).Distinct().ToList()
Dim g2s = fromDb.Select(Function(x) New G2 With
                                    {
                                        .fID = x.G2_ID,
                                        .ContainerID = x.G2_ContainerID,
                                        .SerialNumber = x.G2_SerialNumber
                                    }).Distinct().ToLookup(Function(x) x.fID)
Dim g3s = fromDb.Select(Function(x) New G3 With
                                    {
                                        .fID = x.G3_ID,
                                        .PrinterName = x.G3_PrinterName
                                    }).Distinct().ToLookup(Function(x) x.fID)
g1s.ForEach(Sub(g)
                g.Containers.AddRange(g2s(g.ID))
                g.PrinterNames.AddRange(g3s(g.ID))
            End Sub)

请注意，很多工作都经过了Distinct()和ToLookup()扩展。希望这有所帮助，我想看看是否有更“LINQy”的方法:D

- Tom