My Boise Code Camp Presentation Notes

If you are looking for my Linq2DataSets presentation from Saturday's code camp, I am in the progress of getting them ready and finding a place to post item.

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.