CODEWITHSUNDEEP
c#linq
Ariel
Ariel

Reputation: 916

C# Linq GroupBy and Count field

I need to group some data by its record date, count the occurrence of the values in the field tipoEscola, this field can have the values 1 or 2, and i also need the total amount of records of that grouped date and for each value of tipoEscola. Sorry, i know it's a little confusing so i'm going to show it using the data i have....

These are some records of my DB:

nome    email               tipoEscola          dataCadastro
teste1  [email protected]         2           2015-07-16 10:29:34.140
teste1  [email protected]         1           2015-07-16 10:29:34.140
teste2  [email protected]         2           2015-07-16 10:31:55.337
teste3  [email protected]         2           2015-07-16 12:38:22.403
teste4  [email protected]         1           2015-07-17 13:33:26.973
teste5  [email protected]         2           2015-07-17 13:49:13.850

This is what i managed to do so far:

[0] { Data = {16/07/2015 10:29:34}, Tipo = 2, Qntd = 1 }
[1] { Data = {16/07/2015 10:29:34}, Tipo = 1, Qntd = 1 }
[2] { Data = {16/07/2015 10:31:55}, Tipo = 2, Qntd = 1 }
[3] { Data = {16/07/2015 12:38:22}, Tipo = 2, Qntd = 1 }
[4] { Data = {17/07/2015 13:33:26}, Tipo = 1, Qntd = 1 }
[5] { Data = {17/07/2015 13:49:13}, Tipo = 2, Qntd = 1 }

But this is what i want to achieve:

[0] { Data = {16/07/2015}, Tipo1 = 1, Tipo2 = 3, Qntd = 4 }
[1] { Data = {17/07/2015}, Tipo1 = 1, Tipo2 = 1, Qntd = 2 }

This is the code i used to group:

var leadsPorTipo = db.Lead_SejaEscola.Where(x => EntityFunctions.TruncateTime(x.dataCadastro) >= dataInicio && EntityFunctions.TruncateTime(x.dataCadastro) <= dataFim)
       .GroupBy(n => new { n.dataCadastro, n.tipoEscola })
       .Select(n => new { Data = n.Key.dataCadastro, Tipo = n.Key.tipoEscola, Qntd = n.Count() });

And this is the code i used to format the date:

var chartData = leadsPorTipo.AsEnumerable()
      .Select(x => new {
                  data = x.Data.ToString("dd/MM/yy"),
                  tipo = x.Tipo,
                  quantidade = x.Qntd });

Thanks!

Upvotes: 0

Views: 74

Answers (1)

Ulugbek Umirov
Ulugbek Umirov

Reputation: 12797

Can you try the following?

var leadsPorTipo =
  db.Lead_SejaEscola.Select(x => new { Data = EntityFunctions.TruncateTime(x.dataCadastro),
                                       Tipo = x.tipoEscola })
                    .Where(x => x.Data >= dataInicio && x.Data <= dataFim)
                    .GroupBy(x => x.Data)
                    .Select(g => new { Data = g.Key,
                                       Tipo1 = g.Where(x => x.Tipo == 1).Count(),
                                       Tipo2 = g.Where(x => x.Tipo == 2).Count(),
                                       Qntd = g.Count() })
                    .ToList()

Upvotes: 1

Related Questions