Group By doesn’t appear to work in Linq2DataSet

As I’m working on my presentation for Boise Code Camp, I’ve come across some issues trying to demonstrate Group By. I haven’t been able to make a Group By query work. When I run the same query using Linq2Sql, it works just fine.

For example, given the following code (in VB 10):

Dim MyList = From o In myData.Orders _
    Join od In myData.Order_Details On o.OrderID Equals od.OrderID _
    Join e In myData.Employees On o.EmployeeID Equals e.EmployeeID _
    Group By FullOrder = New With _
        { _
            .OrderId = od.OrderID, _
            .EmployeeName = (e.FirstName & " " & e.LastName), _
            .ShipCountry = o.ShipCountry, _
            .OrderDate = o.OrderDate _
        } _
    Into Amount = Sum(od.Quantity * od.UnitPrice) _
    Where FullOrder.ShipCountry = "USA" _
    Order By FullOrder.OrderId _
    Select FullOrder.OrderId, _
        FullOrder.OrderDate, _
        FullOrder.EmployeeName, _
        Amount

For Each x In MyList
    Console.WriteLine( _
        String.Format( _
           "{0}; {1:d}; {2}: {3:c}", _
           x.OrderId, _
           x.OrderDate, _
           x.EmployeeName, _
           x.Amount))
Next

When myData is a DataSet: I get:

10262; 7/22/1996; Laura Callahan: $204.00
10262; 7/22/1996; Laura Callahan: $360.00
10262; 7/22/1996; Laura Callahan: $60.80
10269; 7/31/1996; Steven Buchanan: $120.00
10269; 7/31/1996; Steven Buchanan: $556.00
10271; 8/1/1996; Michael Suyama: $48.00
10272; 8/2/1996; Michael Suyama: $388.80
10272; 8/2/1996; Michael Suyama: $400.00
10272; 8/2/1996; Michael Suyama: $667.20

There is more than 1 entry for each OrderId, the query failed to group properly. Order 10262 has 3 lines corresponding to the 3 line items of the order.

If I run the same Linq query with myData as a Linq2Sql DataContext, I get:

10262; 7/22/1996; Laura Callahan: $624.80
10269; 7/31/1996; Steven Buchanan: $676.00
10271; 8/1/1996; Michael Suyama: $48.00
10272; 8/2/1996; Michael Suyama: $1,456.00

Here everything is grouped together and Amount is totaled. Order 10262 has 1 entry as I would expect.

There may be a slightly more complex way to make this query work using the DataSet and I haven’t found it, but right now, it doesn’t look good for Linq2DataSets in this area.