Home > Mobile >  Correct way to convert DataTable with duplicate values to Dictionary(of String, List(of Object)) in
Correct way to convert DataTable with duplicate values to Dictionary(of String, List(of Object)) in

Time:12-02

I am trying to convert a datatable to a dictionary. The table format is as follows:

ValueID | DependentValueID | DependencyDescription
__________________________________________________
A       | a                | some text
A       | b                | some text
B       | c                | some text
B       | d                | some text
B       | e                | some text
...

Each object has 3 properties:

  1. ValueID
  2. DependentValueID
  3. DependencyDescription

The Dictionary format is as follows:

(Key) | (Value)
_______________
A    |  {
     |    [ 
     |      ValueID = A
     |      DependentValueID = a
     |      DependencyDescription = "some text"
     |    ], 
     |    [ 
     |      ValueID = A
     |      DependentValueID = b
     |      DependencyDescription = "some text"
     |    ], 
     |  }
-----------------------------------------------
B    |  {
     |    [ 
     |      ValueID = B
     |      DependentValueID = c
     |      DependencyDescription = "some text"
     |    ], 
     |    [ 
     |      ValueID = B
     |      DependentValueID = d
     |      DependencyDescription = "some text"
     |    ], 
     |    [ 
     |      ValueID = B
     |      DependentValueID = e
     |      DependencyDescription = "some text"
     |    ] 
     |  }
...

The code I wrote to do this:

Private Sub MySub()
    ' Declare variables and fill datatables   
    If DependenciesDataTable IsNot Nothing AndAlso DependenciesDataTable.Rows.Count > 0 Then
        For i As Integer = 0 To DependenciesDataTable.Rows.Count - 1
            DependenciesObject.ValueID = DependenciesDataTable.Rows(i).Item("ValueID").ToString
            DependenciesObject.DependentValueID = DependenciesDataTable.Rows(i).Item("DependentValueID").ToString
            DependenciesObject.DependencyDescription = DependenciesDataTable.Rows(i).Item("DependencyDescription").ToString

            If i > 0 Then
                If DependenciesDataTable.Rows(i).Item("ValueID").ToString.Equals(DependenciesDataTable.Rows(i - 1).Item("ValueID").ToString) Then
                    DependenciesObject.Add(DependenciesObject)
                Else
                    DependenciesDictionary.Add(DependenciesDataTable.Rows(i - 1).Item("ValueID").ToString, DependenciesObject)
                    DependenciesObject = New List(Of Object)
                    DependenciesObject.Add(DependenciesObject)
                End If
            ElseIf i = 0 Then
                DependenciesObject.Add(DependenciesObject)
            End If

            If i = DependenciesDataTable.Rows.Count - 1 Then
                DependenciesObject.Add(DependenciesObject)
                DependenciesDictionary.Add(DependenciesDataTable.Rows(i).Item("ValueID").ToString, DependenciesObject)
                DependenciesObject = New List(Of Object)
            End If
        Next
    End If
End Sub

Though this works, I understand that it is a subpar solution since I am a junior developer. I would like some advice on how to improve my approach to the issue.

CodePudding user response:

I see that dependenciesObject is a List(Of Object), but why? Use classes, or even something more temporary like Tuple... My solution will use a class to hold your dependency

Public Class Dependency
    Public Property ValueID As String
    Public Property DependentValueID As String
    Public Property DependencyDescription As String
End Class

Then using LINQ, we can just select from the table into an IEnumerable(Of Dependency).

Dim dependencies = DependenciesDataTable.Select().Select(Function(row) New Dependency With {.ValueID = row("ValueID"), .DependentValueID = row("DependentValueID"), .DependencyDescription = row("DependencyDescription")})

Then group the results by the ValueID property, and select into a Dictionary

Dim dependenciesDictionary = dependencies.GroupBy(Function(d) d.ValueID).ToDictionary(Function(g) g.Key, Function(g) g.Select(Function(v) v).ToList())

You would end up with a Dictionary(Of String, List(Of Dependency))

And print them out to be sure

For Each kvp In dependenciesDictionary
    Console.WriteLine($"Key: {kvp.Key}")
    For Each v In kvp.Value
        Console.WriteLine($"{vbTab}ValueID: {v.ValueID}, DependentValueID: {v.DependentValueID}, DependencyDescription: {v.DependencyDescription}")
    Next
Next

Key: A
ValueID: A, DependentValueID: a, DependencyDescription: some text
ValueID: A, DependentValueID: b, DependencyDescription: some text
Key: B
ValueID: B, DependentValueID: c, DependencyDescription: some text
ValueID: B, DependentValueID: d, DependencyDescription: some text
ValueID: B, DependentValueID: e, DependencyDescription: some text

  • Related