Pivot data in two nested List<T> with Linq

Question

I have the following class structure:

class Employee()
{
  public String Name { get; set; }
  public List<WorkDay> WorkDays { get; set; }
}

class WorkDay()
{
  public DateTime Date { get; set; }
  public Int Hours { get; set; }
}

Is it possible to pivot the List<Employee> with Linq so i have result like this in my DataGridView:

           | Name    | Name    |...| Name      |
Date       | Hours   | Hours   |   | Hours     |
Date       | Hours   | Hours   |   | Hours     |   
Date       | Hours   | Hours   |   | Hours     |
Date       | Hours   | Hours   |   | Hours     |
...        | Hours   | Hours   |   | Hours     |

It's tricky because it's two nested lists and i only found examples with single list which are pretty straightforward. Is it possible to Pivot data using LINQ?

I got to this point but it's not quite there yet:

var _result = Employees.SelectMany(x => x.WorkDays)
                       .GroupBy(x => x.Date)
                       .Select(y => new
                                    {
                                        DATE = y.Key,
                                        NAME = y.Select(z => z.Employee.Name).ToArray()
                                    })
                       .ToList();

I would appreciate any suggestions.

Answer 1

I think you need this:-

var result = employees.SelectMany(x => x.WorkDays, (employeeObj, workDays) => 
                                                   new { employeeObj, workDays })
                      .GroupBy(x => x.workDays.Date)
                      .Select(x => new
                             {
                                Date = x.Key,
                                NameAndHours = x.Select(z => 
                                    new { 
                                            Name = z.employeeObj.Name, 
                                            Hours = z.workDays.Hours 
                                        })
                             }).ToList();

Here is the Working Fiddle with some sample data.

Answer 2

This example uses WPF but you could use the method "PivotWorkingHours" for Winforms or Webforms as well...

Output:

Window:

<Window x:Class="WpfApplication5.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <DataGrid Name="pivotTarget" ItemsSource="{Binding}"/>
    </Grid>
</Window>

Implementation:

public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();

        List<Employee> demo = new List<Employee>
                              {
                                  new Employee{Name = "Frank", WorkDays = new List<WorkDay>
                                                                        {
                                                                            new WorkDay{Date = new DateTime(2001,1,2), Hours = 8},
                                                                            new WorkDay{Date = new DateTime(2001,1,3), Hours = 7},
                                                                        }},
                                  new Employee{Name = "Herbert", WorkDays = new List<WorkDay>
                                                                        {
                                                                            new WorkDay{Date = new DateTime(2001,1,2), Hours = 8},
                                                                            new WorkDay{Date = new DateTime(2001,1,4), Hours = 7},
                                                                        }}

                              };
        pivotTarget.DataContext = PivotWorkingHours(demo);

    }

    private DataTable PivotWorkingHours(IEnumerable<Employee> employees)
    {
        DataTable result = new DataTable();
        result.Columns.Add("Date", typeof(DateTime));
        foreach (string name in employees.Select(x => x.Name).Distinct())
        {
            result.Columns.Add(name, typeof(int));
        }
        foreach (DateTime date in employees.SelectMany(e => e.WorkDays.Select(wd => wd.Date)).Distinct())
        {
            DataRow row = result.NewRow();
            row["Date"] = date;
            foreach (Employee employee in employees)
            {
                row[employee.Name] = employee.WorkDays.Where(wd => wd.Date == date).Sum(wd => wd.Hours);
            }
            result.Rows.Add(row);
        }


        return result;
    }
}

class Employee
{
    public String Name { get; set; }
    public List<WorkDay> WorkDays { get; set; }
}

class WorkDay
{
    public DateTime Date { get; set; }
    public int Hours { get; set; }
}