Using LINQ Group Joins in VB.NET

Question

I'm trying to figure out how to use Group Joins in LINQ queries under VB.NET. For some reason, every example I seem to find on the syntax is just plain WRONG! At least, that's what my compiler keeps telling me. What is it exactly I'm doing wrong here?

This is a simple example where I want to join orders to their order items so that I end up with a type that contains a collection of order items grouped together by their orderId's:

Dim groupedOrders = (From o In orders
                     Group Join i In orderItems On o.OrderId Equals a.OrderId Into myOrders
                     Select o.OrderId, myOrders).ToList()

What I'm currently running into in this example is that the 'myOrders' group I'm creating errors out with:

Definition of method 'myOrders' is not accessible in this context.

Solution 1

In VB, the Into alias needs to be "Group" not myOrders. Using northwind you could state your query as follows:

Dim groupedOrders = 
   From o On Orders
   Group Join od in Order_Details On o.OrderID Equals od.OrderID Into Group
   Select o.OrderID, Details = Group

If you want to alias the group as something else, you can use:

Dim groupedOrders = 
   From o On Orders
   Group Join od in Order_Details On o.OrderID Equals od.OrderID Into GroupedDetails = Group
   Select o.OrderID, GroupedDetails

That being said, if your orders and orderItems are coming from a database provider, you could just use the natural associations and not need the join at all:

Dim groupedOrders = 
   From o In Orders
   Select o.OrderID, Details = o.Order_Details

Also, if you only need to group by the foreign key, you don't need the parent table:

Dim groupedOrders = 
   From od In Order_Details
   Group od By Key = od.OrderID Into Group
   select Key, Group

Solution 2

You're close. You just need to designate myOrders as a Group:

Dim groupedOrders = (From o In orders
                     Group Join i In orderItems On o.OrderId Equals a.OrderId
                     Into myOrders = Group
                     Select o.OrderId, myOrders).ToList()

You can see similar examples, including how to get the group's Count, from this MSDN page: Introduction to LINQ in Visual Basic.