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.

Answer 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

Answer 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.

Answer 3

I like to include another example of a LINQ query, if not to answer this question then just in order to keep a copy that I can refer to in the future:

    Dim result As List(Of Reception_Users)

    result = (From recept In MyBase.QueryGlobalStatic(Of HACRECEP)(Function(x) True)
                  Join sys In MyBase.QueryGlobalStatic(Of SYSESSIO)(Function(x) True) On recept.IdLocking Equals sys.Id
                  Join SYUSRG In MyBase.QueryGlobalStatic(Of SYUser)(Function(x) True) On sys.cle_user Equals SYUSRG.Id
                  Group By SYUSRG.Code, SYUSRG.Nom, SYUSRG.Prenom
                      Into Groupuser = Group
                  Select New Reception_Users With
                      {
                      .CodeUsager = Code,
                      .Nom = Nom,
                      .Prenom = Prenom
                      }).ToList() 

where MyBase.QueryGlobalStatic() is a function that comes from my context in order to allow me to put this kind of code anywhere without any need for checking for connections or authentication data (among other things). The equivalent will be something like:

            result = (From recept In dbContext.HACRECEPs
                  Join sys In dbContext.SYSESSIOs On recept.IdLocking Equals sys.Id
                  Join SYUSRG In dbContext.SYUsers On sys.cle_user Equals SYUSRG.Id
                  Group By SYUSRG.Code, SYUSRG.Nom, SYUSRG.Prenom
                      Into Groupuser = Group
                  Select New Reception_Users With
                      {
                      .CodeUsager = Code,
                      .Nom = Nom,
                      .Prenom = Prenom
                      }).ToList()