CODEWITHSUNDEEP
vb.netlinqgrouping
Lajos Arpad
Lajos Arpad

Reputation: 76787

How to group my LINQ query so that I get the desired result?

I have a few tables which logically form a forest:

Logical context:

We are working with logical filter expressions. tblFilterElement is an atomic logical expression. Many tblFilterElement item can be linked to a tblFilterProd via tblFilterElementsInProd. tblFilterProd represents a composite logical expression where the tblFilterElement items are evaluated and separated with AND. tblFilterSum represents a composite logical expression where tblFilterProd elements are evaluated and separated by OR. I would like to gather a set of tblFilterSum items based on a set of sumIDs, which is a List(Of Integer) and I would like to have a result of

Dim result As List(Of Tuple(Of Integer, List(Of Tuple(Integer, List(Of TblFilterElement)))))

where each result represents a tblFilterSum, item1 will be the SumID of tblFilterSum, item2 will be a List where each element represents a TblFilterProd in the TblFilterSum and of each TblFilterProd, item1 will be the ProdID and item2 will be a List containing all the TblFilterElement items in the prod.

This is my current code:

        Dim elements = (From filterSum In BOs.GeneralBO.GetDataContext.GetTable(Of TblFilterSum)
                        Join filterProdInSum In BOs.GeneralBO.GetDataContext.GetTable(Of TblFilterProdsInSum)
                        On filterSum.SumID Equals filterProdInSum.SumID
                        Join filterProd In BOs.GeneralBO.GetDataContext.GetTable(Of TblFilterProd)
                        On filterProdInSum.ProdID Equals filterProd.ProdID
                        Join filterElementInProd In BOs.GeneralBO.GetDataContext.GetTable(Of TblFilterElementsInProd)
                        On filterProd.ProdID Equals filterElementInProd.ProdID
                        Join filterElement In BOs.GeneralBO.GetDataContext.GetTable(Of TblFilterElement)
                        On filterElementInProd.ElementID Equals filterElement.ElementID
                        Where sumIDs.Contains(filterSum.SumID))

which is great and gathers the data I need, but I just do not know how to group it to gather the result I need. How should I do that?

Upvotes: 0

Views: 34

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76787

This was the solution in lack of knowledge about LINQ group bys:

            Dim elements = (From filterSum In dataContext.GetTable(Of TblFilterSum)
                            Join filterProdInSum In dataContext.GetTable(Of TblFilterProdsInSum)
                        On filterSum.SumID Equals filterProdInSum.SumID
                            Join filterProd In dataContext.GetTable(Of TblFilterProd)
                        On filterProdInSum.ProdID Equals filterProd.ProdID
                            Join filterElementInProd In dataContext.GetTable(Of TblFilterElementsInProd)
                        On filterProd.ProdID Equals filterElementInProd.ProdID
                            Join filterElement In dataContext.GetTable(Of TblFilterElement)
                        On filterElementInProd.ElementID Equals filterElement.ElementID
                            Where sumIDs.Contains(filterSum.SumID)).Select(Function(r) New With {.SumID = r.filterSum.SumID, .ProdID = r.filterProd.ProdID, .filterElement = r.filterElement}).ToList
            'Dim results As New List(Of TTPair(Of Integer, List(Of TTPair(Of Integer, List(Of TblFilterElement)))))
            Dim results As New Dictionary(Of Integer, Dictionary(Of Integer, List(Of TblFilterElement)))
            For Each element In elements
                If (Not results.ContainsKey(element.SumID)) Then
                    results.Item(element.SumID) = New Dictionary(Of Integer, List(Of TblFilterElement))
                End If
                If (Not results.Item(element.SumID).ContainsKey(element.ProdID)) Then
                    results.Item(element.SumID).Item(element.ProdID) = New List(Of TblFilterElement)
                End If
                results.Item(element.SumID).Item(element.ProdID).Add(element.filterElement)
            Next

Explanation:

I added a Select to have objects containing SumID, ProdID and filerElement. Then created a Dictionary and when I iterated elements, checked whether the key of SumID exists. If not, I created it. Then checked whether there is an element with ProdID key inside the element with the key of SumID exists. If not, I created it. Then I added filterElement to the List.

Upvotes: 1

Related Questions