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

CodePudding user response：

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()
        });

CodePudding user response：

You want a pivot table. See code below :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication40
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Email", typeof(string));
            dt.Columns.Add("Status", typeof(string));

            dt.Rows.Add(new object[] { "email1", "opened"});
            dt.Rows.Add(new object[] { "email1", "opened" });
            dt.Rows.Add(new object[] { "email2", "clicked" });
            dt.Rows.Add(new object[] { "email2", "clicked" });
            dt.Rows.Add(new object[] { "email2", "clicked" });
            dt.Rows.Add(new object[] { "email1", "clicked" });
            dt.Rows.Add(new object[] { "email2", "opened" });


            string[] statusTypes = dt.AsEnumerable().Select(x => x.Field<string>("Status")).Distinct().OrderBy(x => x).ToArray();
            var groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("Email")).ToList();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Email",typeof(string));
            foreach(string statusType in statusTypes)
            {
                pivot.Columns.Add(statusType, typeof(int));
            }

            foreach(var group in groups)
            {
                DataRow row = pivot.Rows.Add();
                row["Email"] = group.Key;
                foreach(string statusType in statusTypes)
                {
                    row[statusType] = group.Where(x => x.Field<string>("Status") == statusType).Count();
                }
            }
        }
    }
}