Aggregating one side of a many-to-many and bind to

Question

I have a many-to-many structure in place in my database / Entity Framework model.

CompanyNotice (M-M) CompanyNoticesLocations (M-M) Locations

I am trying to aggregate the Locations for one CompanyNotice and return a comma-separated LocationName for the Locations. I have tried using the following code to aggregate the LocationName:

if (!IsPostBack)
            {
                using (var context = new ALEntities())
                {
                    var query = from c in context.CompanyNotices.Include("Locations")
                                select new 
                                { 
                                    c.CompanyNoticeHeading, 
                                    c.CompanyNoticeText,
                                    c.IsHR,
                                    locations = (from l in c.Locations select l.LocationName).Aggregate((current, next) => string.Format("{0}, {1}", current, next))

                                };
                    ASPxGridView1.DataSource = query;
                    ASPxGridView1.DataBind();
                }   
            }

I get the following error when I try the above code:

LINQ to Entities does not recognize the method 'System.String Aggregate[String](System.Collections.Generic.IEnumerable1[System.String], System.Func3[System.String,System.String,System.String])' method, and this method cannot be translated into a store expression.

When I try:

 if (!IsPostBack)
                {
                    using (var context = new ALEntities())
                    {
                        var query = from c in context.CompanyNotices.Include("Locations")
                                    select new 
                                    { 
                                        c.CompanyNoticeHeading, 
                                        c.CompanyNoticeText,
                                        c.IsHR,
                                        locations = (from l in c.Locations select l.LocationName)

                                    };
                        ASPxGridView1.DataSource = query;
                        ASPxGridView1.DataBind();
                    }   
                }

The data within the locations column on the gridview appears as:

System.Collections.Generic.List`1[System.String]

Does anyone know how do I aggregate the LocationName to appear for one CompanyNotice?

Thanks in advance.

Answer 1

you could this ...

using (var context = new ALEntities()) 
                    { 
                        var query = from c in context.CompanyNotices.Include("Locations") 
                                    select new  
                                    {  
                                        c.CompanyNoticeHeading,  
                                        c.CompanyNoticeText, 
                                        c.IsHR, 
                                        locations = (from l in c.Locations select l.LocationName) 

                                    }; 
var listed = query.ToList();
var commaListed = listed.Select ( a=> new { a.CompanyNoticeHeading, a.CompanyNoticeText,
commaLines =  a.locations.Aggregate((s1, s2) => s1 + "," + s2)});

then bind commaListed to your datagrid