Entity Framework with LINQ aggregate to concatenate string?

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:

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

So, the better idea would be to process/format the data in the presentation layer and let the Data Access layer do only loading or saving raw data that SQL likes. Moreover, in your presentation layer, most probably you are filtering the data by paging, or maybe you are showing one row in the details page, so, the data you will load into the memory is likely smaller than the data you load from the database. (Your situation/architecture may be different,.. but I am saying, most likely).

I had a similar requirement. My problem was to get the list of items from the Entity Framework object and create a formatted string (comma separated value)

1. I created a property in my View Model which will hold the raw data from the repository and when populating that property, the LINQ query won't be a problem because you are simply querying what SQL understands.

2. Then, I created a get-only property in my ViewModel which reads that Raw entity property and formats the data before displaying.

    public class MyViewModel
    {
        public IEnumerable<Entity> 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, I loaded the Data from LINQ to Entity to this View Model easily without calling.ToList().

Example:

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

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

Now, I can call the FormattedData property of MyViewModel anytime when I need and the Getter will be executed only when the property is called, which is another benefit of this pattern (lazy processing).

An architecture recommendation: I strongly recommend to keep the data access layer away from all formatting or view logic or anything that SQL does not understand.

Your Entity Framework classes should be simple POCO that can directly map to a database column without any special mapper. And your Data Access layer (say a Repository that fetches data from your DbContext using LINQ to SQL) should get only the data that is directly stored in your database. No extra logic.

Then, you should have a dedicated set of classes for your Presentation Layer (say ViewModels) which will contain all logic for formatting data that your user likes to see. In that way, you won't have to struggle with the limitation of Entity Framework LINQ. I will never pass my Entity Framework model directly to the View. Nor, I will let my Data Access layer creates the ViewModel for me. Creating ViewModel can be delegated to your domain service layer or application layer, which is an upper layer than your Data Access Layer.