How to return hierarchical anonymous type from LINQ Join?

Question

How can I write a LINQ query that returns an hierachical anonymous type from a Join?

To clarify what I mean: With WCF Data Services and Telerik WPF RadTreeView one can easily query and display hierarchical data:

    Dim q = _ctx1.Execute(Of NorthwindDataService.Customers)(New Uri("Customers\?$expand=Orders", UriKind.Relative))
    Dim td As New GridViewTableDefinition
    td.Relation = New Telerik.Windows.Data.PropertyRelation("Orders")
    RadGridView1.ChildTableDefinitions.Add(td)
    Dim r = q.ToList
    RadGridView1.ItemsSource = r

I want to join entities from different Data Services and display them in a hierachical grid.

Dim q = From c In _ctx1.Customers.ToList
Join o In _ctx2.Orders.ToList On c.CustomerID Equals o.CustomerID
  Select New With {c.CustomerID,
                       o.OrderId
                      }

The join works. How can I "Select" an anonymous type with all properties of Customer plus an Orders property, analogous to what a data service returns, that does directly bind to the grid?

Something like (this is not supported syntax!): Select New With {c.*, .Orders = o }

Answer 1

For two different data services you can use LINQ to Objects:

Dim q1 = _ctx1.Customers.AsEnumerable()
Dim q2 = _ctx2.Orders.ToList()

Dim q = From c in q1
        Select New With {c.CustomerID,
                         q2.Where(o => o.CustomerID == c.CustomerID)
                        }

Again, apologies in advance for VB.NET syntax errors on my part.

For one service:

It's almost always a mistake to use join in LINQ to Entities or LINQ to SQL.

The direct analogy to your data services query is:

Dim q = _ctx1.Customers.Include("Orders");

But you can also project as with your L2E example:

Dim q = From c In _ctx1.Customers.ToList
        Select New With { c.CustomerID,
                          From o in c.Orders Select o.OrderID}

Apologies in advance for syntax errors; VB.NET is not my forte.