none
hierarchical Linq to sql query RRS feed

  • Question

  • I am querying 2 result sets and need to return a nested result set with specific fields.  What is the best way to filter the nested fields out of the query.

    Ex:

    Dim oBallotMeasures = From ballot In dc.sp_SelectBallotMeasures(Nothing, id)
                                  Group Join infoSources In dc.sp_SelectBallotMeasureInfo(Nothing, id)
                                  On ballot.Measure_Id Equals infoSources.Measure_Id
                                  Into MeasureInfo = Group
                                  Select ballot.Measure_id, ballot.Name, MeasureInfo
    

    MeasureInfo includes fields that I do not want to return.  How can I filter out some of the records, but keep the hierarchical structure?

    Desired result:

    [Measure_id: 1
    Name: Name 1
    MeasureInfo: [MeasureInfoName: 1
                        ],
                        [MeasureInfoName: 2
                        ]
    ]
    [Measure_id: 2
    Name: Name 2
    MeasureInfo: [MeasureInfoName: 1
                        ],
                        [MeasureInfoName: 2
                        ]
    ]

    Current result:

    [Measure_id: 1
    Name: Name 1
    MeasureInfo: [MeasureInfoName: 1
                         MeasureInfoOtherInfo: xyz
                        ],
                        [MeasureInfoName: 2
                         MeasureInfoOtherInfo: xyz
                        ]
    ]
    [Measure_id: 2
    Name: Name 2
    MeasureInfo: [MeasureInfoName: 1
                         MeasureInfoOtherInfo: xyz
                        ],
                        [MeasureInfoName: 2
                         MeasureInfoOtherInfo: xyz
                        ]
    ]

    Also, is there any easier way to create a hierarchical result set?


    RobertRFreeman

    Tuesday, March 17, 2015 6:27 PM

Answers

  • Hello RobertRFreeman,

    From your description, it seems that your result sets will already be in local, so you could create DTO class to specify the returned column you want. For example,

    DTO classes:

    Public Class OrderDTO
    
        Property OrderID As Integer
    
        Property OrderName As String
    
        Property OrderDetailName As Object
    
    End Class
    
    Public Class OrderDetailDTO
    
        Property OrderDetailName As String
    
    End Class
    

    The LINQ query:

    Using db As DFDBEntities1 = New DFDBEntities1
    
                Dim result = (From o In db.Orders.ToList
    
                            Group Join od In db.OrderDetails.ToList On o.OrderID Equals od.OrderID
    
                            Into OOD = Group
    
                            Select New OrderDTO With {
    
                                .OrderID = o.OrderID,
    
                                .OrderName = o.OrderName,
    
                                .OrderDetailName = From oood In OOD
    
                                                   Select New OrderDetailDTO With {.OrderDetailName = oood.OrderDetailName}}).ToList
    
            End Using
    

    It will return OrderID, OrderName and a collection which contains OrderDetailName column only I want. Please have a try and note this approach is used LINQ to Objects, while for using LINQ to Entities, unfortunately, the LINQ to Entities does not support such a cast.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 18, 2015 2:52 AM
    Moderator