How to group a list using linq c#

Question

The scenario I have is as follows:

I have the following data -

ID, Name, Type, Location, GroupID

1, samename, Rock, New York, 12
2, samename, Jazz, Sydney, 12
3, samename, Rock, Sydney, 12
4, samename, Jazz, New York, 12    
5, name3, Opera House, Sydney, 14
6, name3, Opera House, London, 14
7, name2, Emirates, London, 13

And I would like to output it flattened based on the GroupID like below

ID, Name, Place, Location, GroupID

1, samename, {Rock,Jazz}, {New York,Sydney}, 12
5, name3, Opera House, {Sydney,London}, 14
7, name2, Emirates, London, 13

This was really bad design that I have inherited - and I am trying to make it better.. without breaking the old code.

I believe the answer is something to do with SelectMany - but I can't work out the syntax - I've tried a few different ways.

my attempted solution - without flattening..

var q3 = Data.Where(b=>b.GroupID != null).GroupBy(x=> new { x.GroupID }, (key, group) => new 
{ 
  GroupID = key.GroupID,  
  Result =  group.Select(g=> new 
                         {                            
                           Type = g.Type, 
                           Location = g.Location,                                                  
                         }).ToList()
});

Answer 1

Try this:

 var q = Data.Where(b => b.GroupID != null).GroupBy((x) => x.GroupID).Select((y) => new
                {
                    GroupID = y.First().ID,
                    Name = string.Join(",", y.Select((k) => k.Name).Distinct()),
                    Type = string.Join(",", y.Select(( k) => k.Type).Distinct()),
                    Location = string.Join(",", y.Select(( k) => k.Location).Distinct()),
                });

If you want to load the columns dynamically,use this code:

        var q2 = Data.Where(b => b.GroupID != null).GroupBy((x) => x.GroupID).Select((y) => new
        {
            GroupID = y.First().ID,
            result = DynamicGroup(y)
        });

    private static string DynamicGroup(IGrouping<string,DataD> y)
    {
        string result=null;
        foreach (System.Reflection.PropertyInfo pInfo in typeof(DataD).GetProperties().Where(x=>x.Name!="GroupID" && x.Name!="ID"))
        {
            result += string.Format(" {0} ; ",string.Join(",", y.Select((k) => pInfo.GetValue(k, null)).Distinct()));
        }
        return result;
    }