C# Linq GroupBy and Count field

Question

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!

Ulugbek Umirov · Accepted Answer · 2015-07-21 19:05:39Z

1

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

edited Jul 21, 2015 at 19:05

answered Jul 21, 2015 at 18:59

Ulugbek Umirov

12.8k3 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ariel Over a year ago

Man, it worked perfectly, only had to change the g.Key.Data to g.Key, thank you very much !

Ulugbek Umirov Over a year ago

@Terkhos Yes, sorry, missed it :)

Ariel Over a year ago

No problem man, you already saved me, i was trying to figure out this thing for sometime now :D

Collectives™ on Stack Overflow

C# Linq GroupBy and Count field

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related