Linq with datatable with two counts c#

Question

I would like to have two different counts in a single query; I wrote it with SQL Server with the COUNT function as

COUNT(CASE WHEN Status = 'opened' THEN 1 ELSE NULL)

which returns my desired count.

However, in the current situation, I have datatable and am not sure if the above two counts are possible in Linq.

My data is as below.

Email      Status 
------------------
email1     opened
email1     opened
email2     clicked
email2     clicked
email2     clicked
email1     clicked
email2     opened

The output needs to be:

Email      Opened      Clicked
-------------------------------
email1      2             1
email2      1             3

Answer 1

Using C# Linq try something like this:

var test =  context.stats.GroupBy(x=> x.Email).Select(groupedBy=> new {
            Email = groupedBy.FirstOrDefault().Email,
            Opened = groupedBy.Where(y=> y.Status == "opened").Count(),
            Clicked = groupedBy.Where(y=> y.Status == "clicked").Count()
        });