VB.NET LINQ - 将分层数据与平面数据匹配

发布于 2024-10-30 08:00:05 字数 3252 浏览 0 评论 0原文

我有来自 2 个不同系统的相似数据的 2 种表示形式,并且我需要将一个系统中的每个实体与另一个系统中的实体进行匹配。

系统 A 是分层的,表示为 Dictionary(Of String, List(Of CategoryA)) ,看起来类似于:

- "Organization 1" 
    - { Name = "Cat1", Id = 1}
    - { Name = "Cat2", Id = 2}
    - { Name = "Cat3", Id = 3}

- "Organization 2" 
    - { Name = "Cat1", Id = 4}
    - { Name = "Cat3", Id = 5}
    - { Name = "Cat4", Id = 6}

- "Organization 3" 
    - { Name = "Cat1", Id = 7}
    - { Name = "Cat2", Id = 8}
    - { Name = "Cat3", Id = 9}
    - { Name = "Cat4", Id = 10}

系统 B 是扁平化的,表示为 List(Of CategoryB) 数据

- { Org = "Organization 1", Name = "Cat1", Id = 100 }
- { Org = "Organization 1", Name = "Cat2", Id = 101 }
- { Org = "Organization 1", Name = "Cat3", Id = 102 }

- { Org = "Organization 2", Name = "Cat1", Id = 103 }
- { Org = "Organization 2", Name = "Cat2", Id = 104 }
- { Org = "Organization 2", Name = "Cat4", Id = 105 }

- { Org = "Organization 4", Name = "Cat1", Id = 106 }
- { Org = "Organization 4", Name = "Cat2", Id = 107 }
- { Org = "Organization 4", Name = "Cat3", Id = 108 }
- { Org = "Organization 4", Name = "Cat4", Id = 109 }

基本上我需要做的是将分层数据外部联接到组织名称(Dictionary.KeyCategoryB.Org)和类别上的扁平化 名称(CategoryA.NameCategoryB.Name),给我留下一个 Dictionary(Of String, IEnumerable(Of Tuple(Of CategoryA, CategoryB)))< /code> 或类似于以下内容的内容:

- "Organization 1" 
    - { Name = "Cat1", Id = 1}, { Org = "Organization 1", Name = "Cat1", Id = 100 }
    - { Name = "Cat2", Id = 2}, { Org = "Organization 1", Name = "Cat2", Id = 101 }
    - { Name = "Cat3", Id = 3}, { Org = "Organization 1", Name = "Cat3", Id = 102 }

- "Organization 2" 
    - { Name = "Cat1", Id = 4}, { Org = "Organization 2", Name = "Cat1", Id = 103 }
    - { Name = "Cat3", Id = 5}, null
    - { Name = "Cat4", Id = 6}, { Org = "Organization 2", Name = "Cat4", Id = 105 }

- "Organization 3" 
    - { Name = "Cat1", Id = 7}, null
    - { Name = "Cat2", Id = 8}, null
    - { Name = "Cat3", Id = 9}, null
    - { Name = "Cat4", Id = 10}, null

我无权访问 CategoryA 对象,无法对其应用组织属性,否则我会这样做并让自己变得更容易。我只是不知道如何连接 Dictionary 键和其值中的一项的属性,并最终得到任何有用的东西。我创建的最成功的实现首先涉及一个 For Each 循环,然后在内部包含一个 LINQ 查询:

Given:
    catA = Dictionary(Of String, List(Of CategoryA))
    catB = List(Of CategoryB)

Dim result As New Dictionary(Of String, List(Of Tuple(Of CategoryA, CategoryB)))

For Each kvp As KeyValuePair(Of String, List(Of CategoryA)) In catA
    Dim orgName As String = kvp.Key

    If Not result.ContainsKey(orgName) Then
        result.Add(orgName, New List(Of Tuple(Of CategoryA, CategoryB)))
    End If

    Dim orgCategories As IEnumerable(Of CategoryB) =
        From cat In catB Where cat.Org = orgName

    Dim tmpResult As IEnumerable(Of Tuple(Of CategoryA, CategoryB)) =
        From cat_a In kvp.Value
        Group Join cat_b In orgCategories
            On cat_a.Name Equals cat_b.Name
        Into matchedCats = Group
        From cat In matchedCats.DefaultIfEmpty
        Select matches = Tuple.Create(cat_a, cat)

    result(orgName).AddRange(tmpResult)
Next

它工作正常,但我希望它位于同一个语句中。

I have 2 representations of similar data from 2 different systems and I need to match each entity in one system with the entities in the other.

System A is Hierarchical, represented as a Dictionary(Of String, List(Of CategoryA)) looking something like:

- "Organization 1" 
    - { Name = "Cat1", Id = 1}
    - { Name = "Cat2", Id = 2}
    - { Name = "Cat3", Id = 3}

- "Organization 2" 
    - { Name = "Cat1", Id = 4}
    - { Name = "Cat3", Id = 5}
    - { Name = "Cat4", Id = 6}

- "Organization 3" 
    - { Name = "Cat1", Id = 7}
    - { Name = "Cat2", Id = 8}
    - { Name = "Cat3", Id = 9}
    - { Name = "Cat4", Id = 10}

System B is Flattened, represented as a List(Of CategoryB) looking something like:

- { Org = "Organization 1", Name = "Cat1", Id = 100 }
- { Org = "Organization 1", Name = "Cat2", Id = 101 }
- { Org = "Organization 1", Name = "Cat3", Id = 102 }

- { Org = "Organization 2", Name = "Cat1", Id = 103 }
- { Org = "Organization 2", Name = "Cat2", Id = 104 }
- { Org = "Organization 2", Name = "Cat4", Id = 105 }

- { Org = "Organization 4", Name = "Cat1", Id = 106 }
- { Org = "Organization 4", Name = "Cat2", Id = 107 }
- { Org = "Organization 4", Name = "Cat3", Id = 108 }
- { Org = "Organization 4", Name = "Cat4", Id = 109 }

And basically what I need to do is to outer join the hierarchical data to the flattened data on Organization name (Dictionary.Key to CategoryB.Org) and Category Name (CategoryA.Name to CategoryB.Name), leaving me with a Dictionary(Of String, IEnumerable(Of Tuple(Of CategoryA, CategoryB))) or something that looks something like:

- "Organization 1" 
    - { Name = "Cat1", Id = 1}, { Org = "Organization 1", Name = "Cat1", Id = 100 }
    - { Name = "Cat2", Id = 2}, { Org = "Organization 1", Name = "Cat2", Id = 101 }
    - { Name = "Cat3", Id = 3}, { Org = "Organization 1", Name = "Cat3", Id = 102 }

- "Organization 2" 
    - { Name = "Cat1", Id = 4}, { Org = "Organization 2", Name = "Cat1", Id = 103 }
    - { Name = "Cat3", Id = 5}, null
    - { Name = "Cat4", Id = 6}, { Org = "Organization 2", Name = "Cat4", Id = 105 }

- "Organization 3" 
    - { Name = "Cat1", Id = 7}, null
    - { Name = "Cat2", Id = 8}, null
    - { Name = "Cat3", Id = 9}, null
    - { Name = "Cat4", Id = 10}, null

I don't have access to the CategoryA object to be able to apply an Organization property to it, or I would do it and make this easier on myself. I just can't figure out how to join on the Dictionary key and a property of one of the items in its value, and end up with anything useful. The most successful implementation I've created involves a For Each loop first, and a LINQ query inside:

Given:
    catA = Dictionary(Of String, List(Of CategoryA))
    catB = List(Of CategoryB)

Dim result As New Dictionary(Of String, List(Of Tuple(Of CategoryA, CategoryB)))

For Each kvp As KeyValuePair(Of String, List(Of CategoryA)) In catA
    Dim orgName As String = kvp.Key

    If Not result.ContainsKey(orgName) Then
        result.Add(orgName, New List(Of Tuple(Of CategoryA, CategoryB)))
    End If

    Dim orgCategories As IEnumerable(Of CategoryB) =
        From cat In catB Where cat.Org = orgName

    Dim tmpResult As IEnumerable(Of Tuple(Of CategoryA, CategoryB)) =
        From cat_a In kvp.Value
        Group Join cat_b In orgCategories
            On cat_a.Name Equals cat_b.Name
        Into matchedCats = Group
        From cat In matchedCats.DefaultIfEmpty
        Select matches = Tuple.Create(cat_a, cat)

    result(orgName).AddRange(tmpResult)
Next

It works alright, but I'd like it to be in the same statement.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

没有你我更好 2024-11-06 08:00:05

嗯,这有点管用,但我坚持使用你的 for 循环!

Public Class CategoryA
    Public Property Name As String
    Public Property Id As Integer
End Class

Public Class CategoryB
    Public Property Org As String
    Public Property Name As String
    Public Property Id As Integer
End Class

Private SystemA As New Dictionary(Of String, List(Of CategoryA))
Private SystemB As New List(Of CategoryB)

Sub Main()
    SystemA.Add("Org1", New List(Of CategoryA) From {New CategoryA() With {.Id = 1, .Name = "Cat1"},
                                                     New CategoryA() With {.Id = 2, .Name = "Cat2"},
                                                     New CategoryA() With {.Id = 3, .Name = "Cat3"}})
    SystemA.Add("Org2", New List(Of CategoryA) From {New CategoryA() With {.Id = 4, .Name = "Cat1"},
                                                     New CategoryA() With {.Id = 5, .Name = "Cat2"},
                                                     New CategoryA() With {.Id = 6, .Name = "Cat3"}})
    SystemA.Add("Org3", New List(Of CategoryA) From {New CategoryA() With {.Id = 7, .Name = "Cat1"},
                                                     New CategoryA() With {.Id = 8, .Name = "Cat2"},
                                                     New CategoryA() With {.Id = 9, .Name = "Cat3"},
                                                     New CategoryA() With {.Id = 10, .Name = "Cat4"}})

    SystemB.Add(New CategoryB() With {.Org = "Org1", .Name = "Cat1", .Id = 100})
    SystemB.Add(New CategoryB() With {.Org = "Org1", .Name = "Cat2", .Id = 101})
    SystemB.Add(New CategoryB() With {.Org = "Org1", .Name = "Cat3", .Id = 102})
    SystemB.Add(New CategoryB() With {.Org = "Org2", .Name = "Cat1", .Id = 103})
    SystemB.Add(New CategoryB() With {.Org = "Org2", .Name = "Cat2", .Id = 104})
    SystemB.Add(New CategoryB() With {.Org = "Org2", .Name = "Cat4", .Id = 105})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat1", .Id = 106})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat2", .Id = 107})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat3", .Id = 108})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat4", .Id = 109})


    Dim AllOrgs = SystemA.Keys.Union(SystemB.Select(Function(b) b.Org).Distinct)

    Dim BothCats2 = From org In AllOrgs
                    Let CatAList = If(Not SystemA.ContainsKey(org), New List(Of CategoryA), From cat In SystemA(org))
                    Let CatBList = (From cat In SystemB Where cat.Org = org).ToList
                    Let AllCatNames = (From cat In CatAList Select cat.Name Distinct).Union(From cat In CatBList Select cat.Name Distinct)
                    Let BothCats = (From cat In AllCatNames
                                    From A In CatAList.Where(Function(CatA) CatA.Name = cat).DefaultIfEmpty
                                    From B In CatBList.Where(Function(CatB) CatB.Name = cat).DefaultIfEmpty)
                    Select org, BothCats

End Sub

Well, this kinda works but id stick with your for loop!

Public Class CategoryA
    Public Property Name As String
    Public Property Id As Integer
End Class

Public Class CategoryB
    Public Property Org As String
    Public Property Name As String
    Public Property Id As Integer
End Class

Private SystemA As New Dictionary(Of String, List(Of CategoryA))
Private SystemB As New List(Of CategoryB)

Sub Main()
    SystemA.Add("Org1", New List(Of CategoryA) From {New CategoryA() With {.Id = 1, .Name = "Cat1"},
                                                     New CategoryA() With {.Id = 2, .Name = "Cat2"},
                                                     New CategoryA() With {.Id = 3, .Name = "Cat3"}})
    SystemA.Add("Org2", New List(Of CategoryA) From {New CategoryA() With {.Id = 4, .Name = "Cat1"},
                                                     New CategoryA() With {.Id = 5, .Name = "Cat2"},
                                                     New CategoryA() With {.Id = 6, .Name = "Cat3"}})
    SystemA.Add("Org3", New List(Of CategoryA) From {New CategoryA() With {.Id = 7, .Name = "Cat1"},
                                                     New CategoryA() With {.Id = 8, .Name = "Cat2"},
                                                     New CategoryA() With {.Id = 9, .Name = "Cat3"},
                                                     New CategoryA() With {.Id = 10, .Name = "Cat4"}})

    SystemB.Add(New CategoryB() With {.Org = "Org1", .Name = "Cat1", .Id = 100})
    SystemB.Add(New CategoryB() With {.Org = "Org1", .Name = "Cat2", .Id = 101})
    SystemB.Add(New CategoryB() With {.Org = "Org1", .Name = "Cat3", .Id = 102})
    SystemB.Add(New CategoryB() With {.Org = "Org2", .Name = "Cat1", .Id = 103})
    SystemB.Add(New CategoryB() With {.Org = "Org2", .Name = "Cat2", .Id = 104})
    SystemB.Add(New CategoryB() With {.Org = "Org2", .Name = "Cat4", .Id = 105})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat1", .Id = 106})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat2", .Id = 107})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat3", .Id = 108})
    SystemB.Add(New CategoryB() With {.Org = "Org4", .Name = "Cat4", .Id = 109})


    Dim AllOrgs = SystemA.Keys.Union(SystemB.Select(Function(b) b.Org).Distinct)

    Dim BothCats2 = From org In AllOrgs
                    Let CatAList = If(Not SystemA.ContainsKey(org), New List(Of CategoryA), From cat In SystemA(org))
                    Let CatBList = (From cat In SystemB Where cat.Org = org).ToList
                    Let AllCatNames = (From cat In CatAList Select cat.Name Distinct).Union(From cat In CatBList Select cat.Name Distinct)
                    Let BothCats = (From cat In AllCatNames
                                    From A In CatAList.Where(Function(CatA) CatA.Name = cat).DefaultIfEmpty
                                    From B In CatBList.Where(Function(CatB) CatB.Name = cat).DefaultIfEmpty)
                    Select org, BothCats

End Sub
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文