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

You use SelectMany if you have a sequence of elements that has a sequence of inner elements and you want to access all sequences of inner elements as one sequence.

SelectMany is similar to the following code:

List<InnerClass> result = new List<InnerClass>()

foreach (var outerElement in outerSequence)
{
    foreach (InnerClass innerElement in outerSequence.InnerSequence)
    {
        // note that InnerSequence is an IEnumerable in every InnerElement
        result.Add(innerElement);
    }
}

You want the opposite: you want to group several elements of your collection into a new sequence. All Names used in your source should result into one element with several fields:

• Name is the name of all elements with the same name
• Id is the lowest Id of all elements this Name
• Place is a sequence of all Type in your sequence with this Name (rock, jazz, emirates) (a bit unlucky name)
• Location is the sequence of all Locations with Name
• GroupId is the GroupId of the items with Name

In your example a Name corresponds with a GroupId. Are you sure there can't be two elements with "samename" and different GroupId?

The first step is to group all elements with the same name using GroupBy:

var result1 = sourceCollection.GroupBy(sourceElement => sourceElement.Name);

Now you have a collection of IGrouping items, each IGrouping item is a sequence of source elements with the same Name, each IGrouping item has a Key property containing this mutual Name.

The second step is to transfer all elements in each group into sequences of Id, sequences of Place and Sequences of GroupId:

var result2 = result1.Select(group => new
{
    Name = group.Key,
    AllIds = group.Select(groupElement => groupElement.Id),
    Places = group.Select(groupElement => groupElement.Place),
    Locations = group.Select(groupElement => groupElement.Location),
    GroupIds = group.Selelect(groupElement => groupElement.GroupId),
};

"From every group in result1, make one new object with a property Name, which contains the Key of the group (which is the common name in all groupElements). This created object also has a property Places, which is from every element in the group the Place. Each create object also has a property Locations, which is... etc."

Now all you have to do is to get the lowest value from AllIds, and the one and only value from Groupids:

var result3 = result2.Select(item => new
{
    Name = item.Name,
    LowestId = item.AllIds.Orderby(id => id).First(),
    Places = item.Places,
    Locations = item.Locations,
    OneAndOnlyGroupId = item.GroupId.First(),
};

If you are not certain that all element with the same Name have the same GroupId, consider grouping by new {Name = sourceElement.Name, GrouId = sourceElement.GroupId), to create groups with same {Name, GroupId}, or let your final OneAndOnlyGroupId be a sequence if GroupIds

Answer 2

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;
    }

Answer 3

Try this:

var answer = data.GroupBy(x => x.GroupID).Select(x => new { 
     ID = x.Min(y => y.ID),
     Name = x.Select(y => y.Name).ToList(),
     Type = x.Select(y => y.Type).ToList(),
     Location = x.Select(y => y.Location).ToList(),
     GroupID = x.Key
}).ToList();