Entity Framework with LINQ aggregate to concatenate string?

Question

This is easy for me to perform in TSQL, but I'm just sitting here banging my head against the desk trying to get it to work in EF4!

I have a table, lets call it TestData. It has fields, say: DataTypeID, Name, DataValue.

DataTypeID, Name, DataValue
1,"Data 1","Value1"
1,"Data 1","Value2"
2,"Data 1","Value3"
3,"Data 1","Value4"

I want to group on DataID/Name, and concatenate DataValue into a CSV string. The desired result should contain -

DataTypeID, Name, DataValues
1,"Data 1","Value1,Value2"
2,"Data 1","Value3"
3,"Data 1","Value4"

Now, here's how I'm trying to do it -

var query = (from t in context.TestData
  group h by new { DataTypeID = h.DataTypeID, Name = h.Name } into g
  select new
 {
   DataTypeID = g.Key.DataTypeID,
   Name = g.Key.Name,
   DataValues = (string)g.Aggregate("", (a, b) => (a != "" ? "," : "") + b.DataValue),
 }).ToList()

The problem is that LINQ to Entities does not know how to convert this into SQL. This is part of a union of 3 LINQ queries, and I'd really like it to keep it that way. I imagine that I could retrieve the data and then perform the aggregate later. For performance reasons, that wouldn't work for my app. I also considered using a SQL server function. But that just doesn't seem "right" in the EF4 world.

Anyone care to take a crack at this?

Answer 1

If the ToList() is part of your original query and not just added for this example, then use LINQ to Objects on the resulting list to do the aggregation:

var query = (from t in context.TestData
            group t by new { DataTypeID = t.DataTypeID, Name = t.Name } into g 
            select new { DataTypeID = g.Key.DataTypeID, Name = g.Key.Name, Data = g.AsEnumerable()})
            .ToList()
            .Select (q => new { DataTypeID = q.DataTypeID, Name = q.Name, DataValues = q.Data.Aggregate ("", (acc, t) => (acc == "" ? "" : acc + ",") + t.DataValue) });

Tested in LINQPad and it produces this result: