In a project that uses Linq2Sql I have the following tables:
+-------------+ +-------------+
| Orders | | OrdItems | +-------------+
+-------------+ +-------------+ | Items |
| OrderID PK | 1---n | OrderID PK | +-------------+
| ... | | ItemID PK | n---1 | ItemID PK |
| | | Quantity | | Description |
+-------------+ | ... | | ... |
+-------------+ +-------------+
In a serarch form I need to let the user provide some criteria and perform a search in Orders.
Then the user selects a single order within search results and this single order is passed to another form.
The search query is something like this:
dim searchResults = From o in myDataContext.Orders Where [...] Select o
Using default DataLoadOptions, at this point I have a list of orders where the OrdItems collection is Nothing for every order.
This is fine because when I perform search I'm not interested in every detail of every order.
When the user selects a single order I would like to populate OrdItems collection with the related Items property.
What is the simplest/most efficient way to do this?
To retrieve OrdItems I could do this:
dim order = searchResults.First() ' The user actually selects an order
order.OrdItems.Tolist()
but then I have to cycle through every OrdItems to retrieve Items value.
I could customize DataLoadOptions this way:
dim dlo = New DataLoadOptions()
dlo.LoadWith(Of OrdItems)(Function(o) o.Items)
myDataContext.LoadOptions = dlo
Unfortunately I can't do this after the search query has run, and if I do this before then details are loaded for every order.
Another option would be to create a new DataContext with different LoadOptions so that OrdItems and Items are loaded along with Orders and perform another query for the selected order.
However, this way Orders table is queried twice.
