Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
642 views
in Technique[技术] by (71.8m points)

c# - Entity Framework with LINQ aggregate to concatenate string?

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

alt text


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...