Creating a matrix from table in c#

Question

With .Net MVC i am using DB First approach. I need a School Day Program, but I am not sure to use pivoting, DataTable or anything else?

The table in SQL is like that:

WeeklyProgram
Id RoomId Hour Day
1  1      9    1
2  1      12   1
3  2      14   1
4  2      12   2
5  3      11   1
6  4      10   2

And trying to convert it to a table in html with null cells like this:

For Day 1 My School Program is;
#     Room1 Room2 Room3
9:00  1     null  null
10:00 null  null  null
11:00 null  null  5
12:00 2     null  null
13:00 null  null  null
14:00 null  3     null

Tried pivoting but cant get what i thought.

Thanks all.

UPDATE: I'VE FOUND AN ANSWER

if (!date.HasValue)
            date = DateTime.Now;

        List<WeeklyProgram> modelBase = table
            .Where(b => b.Day == day)
            .ToList();

        int[] hours = { 9, 10, 11, 12, 13, 14};

        List<PivotTable> pivotTable = new List<PivotTable>();

        foreach (var hour in hours)
        {
            PivotTable pivotRow = new PivotTable() { Hour = hour };

            foreach (var item in modelBase)
                if (item.Hour == hour)
                    switch (item.RoomId)
                    {
                        case 1:
                            pivotRow.Room1 = item.Id;
                            break;
                        case 2:
                            pivotRow.Room2 = item.Id;
                            break;
                        case 3:
                            pivotRow.Room3 = item.Id;
                            break;
                    }
            pivotTable.Add(pivotRow);
        }

        return pivotTable;

--

    public class PivotTable
{
    public int Hour { get; set; }
    public int Room1 { get; set; }
    public int Room2 { get; set; }
    public int Room3 { get; set; }
    public int Room4 { get; set; }
    public int Room5 { get; set; }
}

Answer 1

First, you need to filter your WeeklyProgram down to the day of interest:

var dayProgram = from c in WeeklyProgram
                 where c.Day == 1
                 select c;

Then you can extract the hourly schedule rows from that data, leaving nulls for unscheduled rooms by converting Id into a nullable type, which is then converted to a Dictionary for lookup later:

var dayClasses = (from c in dayProgram
                  group c by c.Hour into cg
                  let cgd = cg.ToDictionary(c => c.RoomId, c => (int?)c.Id)
                  select new {
                      Hour = cg.Key,
                      Room1 = cgd.GetValueOrDefault(1),
                      Room2 = cgd.GetValueOrDefault(2),
                      Room3 = cgd.GetValueOrDefault(3)
                  }).ToDictionary(c => c.Hour, c => c);

Now you extract all the hours that are used during the WeeklyProgram:

var hoursInWeek = (from c in WeeklyProgram
                   orderby c.Hour
                   select c.Hour).Distinct();

Now you are ready to combine the hours used with the rooms scheduled:

var ans = from h in hoursInWeek
          select new {
              Hour = $"{h}:00",
              Room1 = dayClasses.GetValueOrDefault(h)?.Room1,
              Room2 = dayClasses.GetValueOrDefault(h)?.Room2,
              Room3 = dayClasses.GetValueOrDefault(h)?.Room3
          };

This uses a helper function to make looking up in dictionaries where the key might not exist easier (NOTE: This uses all features up to C# 7.1.)

static public class Ext {
    public static TV GetValueOrDefault<TK, TV>(this IDictionary<TK, TV> dict, TK key, TV defaultValue = default) => dict.TryGetValue(key, out TV value) ? value : defaultValue;
}

For earlier versions of C#, you can use this version:

static public class Ext {
    public static TV GetValueOrDefault<TK, TV>(this IDictionary<TK, TV> dict, TK key, TV defaultValue = default(TV)) => dict.TryGetValue(key, out TV value) ? value : defaultValue;
}