title: Entity Framework with LINQ aggregate to concatenate string?
body: <p>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!</p>
  
  <p>I have a table, lets call it TestData. It has fields, say: DataTypeID, Name, DataValue.</p>
  
  <pre><code>DataTypeID, Name, DataValue
  1,"Data 1","Value1"
  1,"Data 1","Value2"
  2,"Data 1","Value3"
  3,"Data 1","Value4"
  </code></pre>
  
  <p>I want to group on DataID/Name, and concatenate DataValue into a CSV string. The desired result should contain -</p>
  
  <pre><code>DataTypeID, Name, DataValues
  1,"Data 1","Value1,Value2"
  2,"Data 1","Value3"
  3,"Data 1","Value4"
  </code></pre>
  
  <p>Now, here's how I'm trying to do it -</p>
  
  <pre><code>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) =&gt; (a != "" ? "," : "") + b.DataValue),
   }).ToList()
  </code></pre>
  
  <p>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.</p>
  
  <p>Anyone care to take a crack at this?</p>