How do i count a column of bools grouped by date in LINQ?

Question

I have a table that holds 15 session bookings by dates. Each row is a persons db entry for the sessions they attend.

For each date I need to count the bools for each session S1, S2 etc

How many are in S1 for 1/1/2019?.

Here is the table in asp.net core

![Pic of the table][https://imgur.com/a/SguJmLI]

It looks like this

Session   S1  S2  S3  S4  S5  S6  S7
1/1/2019   1  0    1   1   1   0   1
1/1/2019   0  1    1   1   1   1   1
2/1/2019   0  0    1   1   1   1   1
2/1/2019   0  1    1   1   1   1   1

There are multiple session dates, and I need to get a summary count of the bools for each date.

1/1/2019   1  1    2   2   2   1   2
2/1/2019   0  1    2   2   2   2   2

Sadly I havn't got a single thing to generate

But this is how I am generating the grid

 return View(await _context.SeatBooking
           .OrderByDescending(s => s.SeatDate)
           .ToListAsync());

Answer 1

Since the Types of columns are bool, the first to do is to convert the bool to int .

Func<bool, int> convertBoolToInt = flag => flag ? 1 : 0;

var query = _context.SeatBooking
    .Select(s => new SeatBookingDto
    {
        SeatDate = s.SeatDate,
        S1 = convertBoolToInt(s.S1),
        S2 = convertBoolToInt(s.S2),
        S3 = convertBoolToInt(s.S3),
        S4 = convertBoolToInt(s.S4),
        S5 = convertBoolToInt(s.S5),
        S6 = convertBoolToInt(s.S6),
        S7 = convertBoolToInt(s.S7),
    });

And now we can group and calculate the sum of each column for each group:

query.GroupBy(m => m.SeatDate, m => m, (k, g) => new
{
    Date = k,
    S1= g.Sum(m=>m.S1),
    S2= g.Sum(m=>m.S2),
    S3= g.Sum(m=>m.S3),
    S4= g.Sum(m=>m.S4),
    S5= g.Sum(m=>m.S5),
    S6= g.Sum(m=>m.S6),
    S7= g.Sum(m=>m.S7),
})

If you're using the EFCore , the corresponding query and result are :

App>       SELECT [s].[SeatDate], [s].[S1], [s].[S2], [s].[S3], [s].[S4], [s].[S5], [s].[S6], [s].[S7]
App>       FROM [SeatBooking] AS [s]
App>       ORDER BY [s].[SeatDate]
App> 1/1/2019 12:00:00 AM : 1,1,2,2,2,1,2
App> 2/1/2019 12:00:00 AM : 0,1,2,2,2,2,2

Note the LINQ expression g.sum() here are all evaluated locally which means not evaluated by database . It seems that the EFcore has not evolved to be able to translate the Aggregate expression (such as Sum) to SQL query that can be executed on server .

If you want to query objects in memory only , there's another way to do that (not supported by EFCore yet) :

query.GroupBy(m=>m.SeatDate,m=>m,(k,g)=>new {
    Date = k,
    Count = g.Aggregate((c,n)=>new SeatBookingDto{
        SeatDate=c.SeatDate,
        S1=c.S1+n.S1,
        S2=c.S2+n.S2,
        S3=c.S3+n.S3,
        S4=c.S4+n.S4,
        S5=c.S5+n.S5,
        S6=c.S6+n.S6,
        S7=c.S7+n.S7,
    }) 
})