views:

118

answers:

2

I have a Collection on Menu Sections which each contain a collection of Menu Items (it's a two-tier menu, simple stuff). Ideally I could deepload the MenuSection collection to grab the MenuItems atthe same time, but failing that, is there a way that I can return a separate Collection of each using only one database call via subsonic? Am I stuck using a DataSet with multiple tables?

A: 

Yes - there are ways to do this - many different approaches. You can use a View and load everything, then spin that out to a structured object set. Or you could run a joined query into a datareader and load the objects with some looping logic.

Rob Conery
Rob, thanks for the reply. I'm not really clear on what you mean by "spin that out" -- do you mean I should load a collection of rows that have the data from both menu sections and items?With the reader and looping, wouldn't that mean getting a collection of sections, then a separate call for each group of items?
Pete Michaud
By "spin that out" i mean loop over the the reader and assign the values to a collection. If you use a view then you can get a flat representation of the data. If you use a joined query - same thing - a flat representation of the data that you fill the two collections with.
Rob Conery
A: 

What Rob said is correct. Another angle would be like so:

Partial Public Class MenuSection

Private _ChildMenus As Generic.List(Of MenuSection)
Public Property ChildMenus() As Generic.List(Of MenuSection)
    Get
        If _ChildMenus Is Nothing Then
            ' Load the data in the list
            _ChildMenus = New SubSonic.Select().From(Data.MenuSelection). _
            Where("ParentMenuItemId").IsEqualTo(1). _
            ExecuteTypedList(Of Data.MenuSelection)()
        End If
        Return _ChildMenus
    End Get
    Set(ByVal value As Generic.List(Of MenuSection))
        _ChildMenus = value
    End Set
End Property

End Class

Rick Ratayczak