CODEWITHSUNDEEP
linq
Randy Minder
Randy Minder

Reputation: 48522

Linq - How to convert from flat to hierarchical?

I have the following result set that was generated as the result of a Linq query. I would like to convert this to a hierarchical result set. The first two columns would represent the 'master' row, columns 3 and 4 would represent a child list of the master row, and column 5 and 6 would represent a second child list of the master row. The columns containing the value 1971 are the joining columns.

The end result should be a master object with a list of containers (G2 columns) and a list of printers (G3 columns).

What would the query look like to convert this to hierarchical form?

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

Upvotes: 6

Views: 1446

Answers (2)

Bonshington
Bonshington

Reputation: 4032

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;

haven't test it. but i'm sure its gonna look just like this.

Upvotes: 3

Tom
Tom

Reputation: 3374

Ok, this is quite a through provoking question. I've done a lot of data flattening in the past, and typically I'd use a Dictionary to keep all the unique values, then marry them up afterwards.

You asked for LINQ, now I cannot think of a single-pass way to do this, so I've got this instead, in 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)

Notice that quite a lot of the work has gone through the Distinct() and ToLookup() extensions. Hope this helps, I'd like to see if there is a more "LINQy" way :D

Upvotes: 1

Related Questions