Grouping Query results by multiple columns in Linq

Recently I came across a requirement where I had to create a custom excel report based on grouping concept of SQL. Usage of Group by clause and Group new clause are already there on internet but there combined usage and creation of custom columns in excel is something I felt is missing. So here is my blog solving such problems:-

Assuming 2 tables as shown below:-

1. Machine Table

2. Event Table

Combined Result in SQL

Required Output in excel format

In this example, we are grouping by UniqueId and also formatting the columns Action and GroupName.

We will create a entity class for Report as shown below, List all columns that are needed in excel Report. I have added UniqueId, Action and Group.

   1: public class Report

   2: {        

   3:     public string UniqueId { get; set; }       

   4:     public string ActionName { get; set; }

   5:     public string GroupName { get; set; }

   6: }

Below Method ‘GenerateCustomReport’ returns List of Report object, this return value can be exported into excel in calling function.

In this method, we are performing following steps:-

1. Read table data into variable (I am using ADO.Net Entity framework in this case, you can also use SQL methods to read data from Machine and Event tables)

2. Using Linq to join tables and group by Unique Id and store result in ‘Report’ Entity format.

3. Iterate through ‘Report’ Entity result. Customize Action and Group Column as per the requirement. Here I am appending values with counter number like 1., 2. … etc.

   1: public List<Report> GenerateCustomReport()

   2: {

   3:     List<Report> finalReportList = new List<Report>();

   4:  

   5:     var ctx = new DMT2ModelContainer();

   6:     var EventLogs = ctx.EventLogs.ToArray();

   7:     var Machines = ctx.Machines.ToArray();

   8:  

   9:     var result = from eventLog in EventLogs

  10:                  join machine in Machines on eventLog.MachineId equals machine.Id

  11:                  group new { machine.UniqueId, eventLog.CMActionName, eventLog.CMGroupName } by machine.UniqueId into report

  12:                  select report;

  13:  

  14:     foreach (var groupedRow in result)

  15:     {

  16:         Report finalReportRow = new Report();

  17:         int counter = 1;

  18:         foreach (var row in groupedRow)

  19:         {

  20:             finalReportRow.UniqueId = row.UniqueId;

  21:             finalReportRow.ActionName += counter + ". " + row.CMActionName + Environment.NewLine;

  22:             finalReportRow.GroupName += counter + ". " + row.CMGroupName + Environment.NewLine;

  23:             counter++;

  24:         }

  25:         finalReportList.Add(finalReportRow);

  26:     }

  27:  

  28:     return finalReportList;

  29: }

I have focused on column customization and group by logic. On how to create excel and exporting data into excel , there are multiple blogs available on internet that shows how to achieve this functionality.

You may need to call the above method and export the return data into excel. The final output will look like below:-

Write to me for suggestions or feedback.

Till then, Happy Coding!!!