Group items in a list based on two properties using LINQ

Question

I have a Column class as below:

public class Column
{
    public int LocId { get; set; }
    public int SecId { get; set; }
    public double StartElevation { get; set; }
    public double EndElevation { get; set; }
}

And a list of Column objects:

List<Column> Columns = new List<Column>();

For example:

Columns:
{
    Column1: { LocId = 1 , SecId = 1, StartElevation = 0, EndElevation = 160 }
    Column2: { LocId = 1 , SecId = 1, StartElevation = 160, EndElevation = 320 }
    Column3: { LocId = 1 , SecId = 2, StartElevation = 320, EndElevation = 640 }
    Column4: { LocId = 2 , SecId = 1, StartElevation = 0, EndElevation = 160 }
    Column5: { LocId = 2 , SecId = 2, StartElevation = 160, EndElevation = 320 }
}

I want to apply the below algorithm to the above list using Linq.

Go through the Columns list and:

• (A) Choose a list of items that have the the same LocId.

• (B) Then from that list choose another list of items that have the same SecId.

This will give me a list hopefully which I will perform other things on it.

So applying the above algorithm on the data above will be like this.

                                       Columns List
                ---------------------------------------------------------
               | Column1     Column2     Column3     Column4     Column5 |
                ---------------------------------------------------------
                                            |
                                            |
                                           (A)
                                            |
                                            |
                        --------------------------------------------
                        |                                          |
                GroupBasedOnLocId                         GroupBasedOnLocId
                        |                                          |
                  -----------                                 -----------
                  | Column1 |                                 | Column4 |
                  | Column2 |                                 | Column5 |
                  | Column3 |                                 -----------
                  -----------                                      |
                       |                                           |
                      (B)                                         (B)
                       |                                           |
          -------------------------                   -------------------------
          |                       |                   |                       |
          |                       |                   |                       |
 GroupBasedOnSecId        GroupBasedOnSecId  GroupBasedOnSecId        GroupBasedOnSecId
          |                       |                   |                       |
          |                       |                   |                       |
       Column1                 Column3             Column4                 Column5
       Column2

How can I accomplish this using LINQ?

Answer 1

Use .GroupBy with composite key.

Sample code:

List<Column> Columns = new List<Column>
{
   new Column { LocId = 1 , SecId = 1, StartElevation = 0, EndElevation = 160 },
   new Column { LocId = 1 , SecId = 1, StartElevation = 160, EndElevation = 320 },
   new Column { LocId = 1 , SecId = 2, StartElevation = 320, EndElevation = 640 },
   new Column { LocId = 2 , SecId = 1, StartElevation = 0, EndElevation = 160 },
   new Column { LocId = 2 , SecId = 2, StartElevation = 160, EndElevation = 320 }
};

foreach (var group in Columns.GroupBy(c => new { c.LocId, c.SecId }))
{
    Console.WriteLine("group: LocId = {0}, SecId = {1}", group.Key.LocId, group.Key.SecId);
    foreach(Column column in group)
        Console.WriteLine("  item: StartElevation = {0}, EndElevation = {1}", column.StartElevation, column.EndElevation);
}

You can transform the group any way you want:

foreach (var res in Columns.GroupBy(c => new { c.LocId, c.SecId })
                           .Select(g => new
                           {
                               g.Key.LocId,
                               g.Key.SecId,
                               MinStartElevation = g.Min(c => c.StartElevation),
                               MaxEndElevation = g.Max(c => c.EndElevation)
                           }))
{
    Console.WriteLine("LocId = {0}, SecId = {1}, MinStartElevation = {2}, MaxEndElevation = {3}",
        res.LocId, res.SecId, res.MinStartElevation, res.MinStartElevation);
}

Answer 2

If you want a two-level grouping as your diagram indicates, you need to use GroupBy twice:

var grouping = columns
  .GroupBy(col => new { col.LocId, col.SecId }) // create groups by LocId+SecId
  .GroupBy(group => group.Key.LocId); // re-group by LocId only

Then you'll have a sequence of groups, each group having a having an int key (that's the LocId) and consisting of a sequence of other groups, each one having a composite key of both LocId and SecId, and consisting of a sequence of columns (matching those LocId and SecId).

You can then access the two-level grouping by foreach-ing over each level. For example:

foreach (var locGroup in grouping) {
  Console.WriteLine("LocId: " + locGroup.Key)

  foreach (var secGroup in locGroup) {
    Console.WriteLine("  SecId:" + secGroup.Key.SecId)
      Console.WriteLine("  Min StartElevation: {0}", 
        secGroup.Min(col => col.StartElevation);
      Console.WriteLine("  Max EndElevation: {0}", 
        secGroup.Max(col => col.EndElevation);

      foreach (var column in secGroup) {
        Console.WriteLine("    {0} -> {1}", column.StartElevation, column.EndElevation);
      }
    }
  }
}

Or, if you want to be able to find a specific node in the tree, you can use ToDictionary and ToLookup:

var lookup = columns
  // group columns by LocId
  .GroupBy(col => col.LocId) 
  // create a dictionary from the groups to find them by LocId,
  // where the value of each entry is a lookup of its own columns by SecId
  .ToDictionary(             
     locGroup => locGroup.Key, 
     locGroup => locGroup.ToLookup(col => col.SecId));

Then you could do things like:

var locId = "123";
var locGroup = lookup[locId];
Console.WriteLine("LocId {0} has {1} sub-groups", locId, locGroup.Count);
Console.WriteLine("LocId {0} has {1} total columns", locId, 
  locGroup.Sum(secGroup => secGroup.Count()));

var secId = "456";
var secGroup = locGroup[secId];
Console.WriteLine("LocId {0}, SecId {1} has {2} columns", 
  locId, secId, secGroup.Count());

Answer 3

Here is a solution that uses LINQ Query Syntax. (Query syntax and Method Syntax are semantically identical, but many people find query syntax simpler and easier to read.)

// Declare and then populate the LINQ source.
List<Column> columns = new List<Column>();

var query =
    from column in columns
    group column by new {column.LocId, column.SecId} into g
    orderby g.Key.LocId, g.Key.SecId
    select new
    {
        LocId = g.Key.LocId,
        SecId = g.Key.SecId,
        Columns = g
    };

Below you'll find a complete demonstration program for this LINQ query using the data you provided. I've preceded the demo program with its expected output. Also see the live demo.

Expected Output

LocId:1, SecId:1
  StartElevation:0, EndElevation:160
  StartElevation:160, EndElevation:320
LocId:1, SecId:2
  StartElevation:320, EndElevation:640
LocId:2, SecId:1
  StartElevation:0, EndElevation:160
LocId:2, SecId:2
  StartElevation:160, EndElevation:320

Program

using System;
using System.Collections.Generic;
using System.Linq;

class LinqGroupDemo
{
    static public void Main(string[] args)
    {
        var query =
            from column in GetSource()
            group column by new {column.LocId, column.SecId} into g
            orderby g.Key.LocId, g.Key.SecId
            select new
            {
                LocId = g.Key.LocId,
                SecId = g.Key.SecId,
                Columns = g
            };

        foreach (var key in query)
        {
            Console.WriteLine("LocId:{0}, SecId:{1}",
                              key.LocId,
                              key.SecId);

            foreach (var column in key.Columns)
            {
                Console.WriteLine("  StartElevation:{0}, EndElevation:{1}",
                                  column.StartElevation,
                                  column.EndElevation);
            }
        }
    }

    static private List<Column> GetSource()
    {
        return new List<Column>
        {
            new Column { LocId = 1 , SecId = 1, StartElevation = 0, EndElevation = 160 },
            new Column { LocId = 1 , SecId = 1, StartElevation = 160, EndElevation = 320 },
            new Column { LocId = 1 , SecId = 2, StartElevation = 320, EndElevation = 640 },
            new Column { LocId = 2 , SecId = 1, StartElevation = 0, EndElevation = 160 },
            new Column { LocId = 2 , SecId = 2, StartElevation = 160, EndElevation = 320 }
        };
    }
}

public class Column
{
    public int LocId { get; set; }
    public int SecId { get; set; }
    public double StartElevation { get; set; }
    public double EndElevation { get; set; }
}

Answer 4

Use GroupBy:

var results = columns
    .GroupBy(column => column.LocId)
    .Select(group => group.GroupBy(c => c.Sec.Id));