Entity Framework with LINQ aggregate to concatenat

问题:

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?

回答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:

回答2:

Thanks to moi_meme for the answer. What I was hoping to do is NOT POSSIBLE with LINQ to Entities. As others have suggested, you have to use LINQ to Objects to get access to string manipulation methods.

See the link posted by moi_meme for more info.

Update 8/27/2018 - Updated Link (again) - https://web.archive.org/web/20141106094131/http://www.mythos-rini.com/blog/archives/4510

And since I'm taking flack for a link-only answer from 8 years ago, I'll clarify just in case the archived copy disappears some day. The basic gist of it is that you cannot access string.join in EF queries. You must create the LINQ query, then call ToList() in order to execute the query against the db. Then you have the data in memory (aka LINQ to Objects), so you can access string.join.

The suggested code from the referenced link above is as follows -

var result1 = (from a in users
                b in roles
           where (a.RoleCollection.Any(x => x.RoleId = b.RoleId))
           select new 
           {
              UserName = a.UserName,
              RoleNames = b.RoleName)                 
           });

var result2 = (from a in result1.ToList()
           group a by a.UserName into userGroup
           select new 
           {
             UserName = userGroup.FirstOrDefault().UserName,
             RoleNames = String.Join(", ", (userGroup.Select(x => x.RoleNames)).ToArray())
           });

The author further suggests replacing string.join with aggregate for better performance, like so -

RoleNames = (userGroup.Select(x => x.RoleNames)).Aggregate((a,b) => (a + ", " + b))

回答3:

Some of the Answers suggest calling ToList() and then perform the calculation as LINQ to OBJECT. Thats fine for little amount of data, but if I have huge amount of data what I do not want to load into memory too early, then, ToList() may not be an option.

I had a similar requirement. My problem was to get the list of child items of an entity and create a comma separated value string with the first character of that child item.

1. I created a property in my View Model which will hold the raw data from repository.

   public class MyViewModel
   {
       public string AnotherRegularProperty { get; set; }
   
       public IEnumerable<string> RawChildItems { get; set; }
   
       public string FormattedData
       {
           get
           {
               if (this.RawChildItems == null)
                   return string.Empty;
   
               string[] theItems = this.RawChildItems.ToArray();
   
               return theItems.Length > 0
                   ? string.Format("{0} ( {1} )", this.AnotherRegularProperty, String.Join(", ", theItems.Select(z => z.Substring(0, 1))))
                   : string.Empty;
           }
       }
   }
   

Ok, in that way, my ViewModel was ready. After that, I loaded the Data from LINQ to Entity to this View Model easily without calling .ToList() which would have loaded all data to memory. If the database had thoudands of records, i would never call .ToList().

Example:

IQueryable<MyEntity> myEntities = _myRepository.GetData();

IQueryable<MyViewModel> viewModels = myEntities.Select(x => new MyViewModel() { AnotherRegularProperty = x.AProperty, RawChildItems = x.MyChildren })

Now, I can call FormattedData property of MyViewModel anytime when I need and the Getter will be executed only when the property is called.

回答4:

Maybe it's a good idea to create a view for this on the database (which concatenates the fields for you) and then make EF use this view instead of the original table?

I'm quite sure it's not possible in a LINQ statement or in the Mapping Details.

回答5:

You are so very close already. Try this:

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.Join(",", g),
 }).ToList()

Alternatively, you could do this, if EF doesn't allow the String.Join (which Linq-to-SQL does):

var qs = (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 = g
 }).ToArray();

var query = (from q in qs
            select new
            {
                q.DataTypeID,
                q.Name,
                DataValues = String.Join(",", q.DataValues),
            }).ToList();