views:

78

answers:

2

I'm getting data from the data layer that I need to transform in the middle tier using Linq To Objects (I don't control that tier of the application). I have to perform a grouping operation using multiple keys followed by a string concatenation on the non key-fields. Given the data below, I want to group by CustomerID and Date, and create a new structure in which only a single row is generated based on the key, and the non key field (in this case Item) gets combined into one string:

    CustomerID  Date         Item
    A          11/1/2001    Bread
    A          11/1/2001        Orange Juice
    A          11/1/2001        Salad Dressing
    B          11/1/2001    Bananas
    C          5/6/2001     Candy
    C          12/8/2005    Candy               


    CustomerID  Date         Item
    A          11/1/2001    Bread
                             Orange Juice
                             Salad Dressing
    B          11/1/2001    Bananas
    C          5/6/2001     Candy
    C          12/8/2005    Candy

Is this possible using the functional style linq syntax or do I have to resort to old-school imperative foreach syntax?

+1  A: 

Something like this?

var result = from item in items
             group item by new { item.Date, item.CustomerID } into g
             select new
             {
                 g.Key.CustomerID,
                 g.Key.Date,
                 Item = string.Join(", ", from i in g select i.Item)
             };
dtb
Only the .Min date? Doesn't it need to be grouped twice?
Zachary Yates
@Zachary Yates: What?
dtb
@dtb You changed it before I could finish posting the comment :)
Zachary Yates
+2  A: 

Alternatively:

var results = items
  .GroupBy(i => new { i.Date, i.CustomerID })
  .Select(g => new {
    CustomerID = g.Key.CustomerID,
    Date = g.Key.Date,
    Item = string.Join(", ", g.Select(i => i.Item).ToArray())
  });
Zachary Yates
@Zachary Yates: I wrote up the same thing in VS, but the compiler complained about including an IEnumerable<> as a parameter to string.Join(). This will work: "g.Select(i => i.Item).ToArray()".
Jimmy W
@Jimmy W: [String.Join](http://msdn.microsoft.com/en-us/library/dd992421.aspx) has an overload that takes any IEnumerable<> since .NET 4.0.
dtb
Ah, I was using .NET 3.5. Makes sense.
Jimmy W
Am using .NET 3.5; Sorry should have mentioned that in the question.
Dirk
@Dirk: Then just add `.ToArray` as *@Jimmy W* indicated, and you should be fine.
dtb