How to perform a Count in multiple columns?

Question

I’d like to make a COUNT using LINQ / LAMBDA, but I’m having difficulties. Can help me?

The idea is to reproduce the following query:

SELECT Conta, Tipo, Documento, Nome, COUNT(0) AS Qtde 
FROM TaxaPrecificacao
GROUP BY Conta, Tipo, Documento, Nome

Below are a few ways I tried to do but, of course, they are all wrong.

//Funciona mas a coluna do Count não está sendo calculada
var view = TaxaPrecificacao
.Select(
x => new PrecTxContas
{
    Conta = x.Conta,
    Tipo = x.Tipo,
    Documento = x.Documento,
    Nome = x.Nome,
    Qtde = 0
});


//Erro de sintaxe
var view = TaxaPrecificacao
.Select(
x => new PrecTxContas
{
    Conta = x.Conta,
    Tipo = x.Tipo,
    Documento = x.Documento,
    Nome = x.Nome,
    Qtde = x.Count() 
});


/*Sintaxe OK mas não compila The type appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order*/
var view = TaxaPrecificacao
.GroupBy(
x => new PrecTxContas
{
    Conta = x.Conta,
    Tipo = x.Tipo,
    Documento = x.Documento,
    Nome = x.Nome
})
.Select
(
x => new PrecTxContas
{
    Conta = x.Conta,
    Tipo = x.Tipo,
    Documento = x.Documento,
    Nome = x.Nome,
    Qtde = x.Count()
});

Answer 1

You need to keep in mind that the GroupBy returns a grouping structure.

This structure contains all the data of a given grouping and also the key (Key) from it. For example: if you have 5 items whose grouping keys are Conta = 01, Tipo = 1, Documento = 2 and Nome = "Foo", this structure will contain, among other things, all data of the grouping and a member Key which will also be a structure with all the members of the grouping, something like:

{ Conta = 1, Tipo = 1, Documento = 2, Nome = "Foo" }

Then, after using the method GroupBy(), you can work upon this structure and use the method Count() to return the amount of elements of each grouping, or Sum() to add up the values of some property and so on.

The code would basically look like this:

var view = TaxaPrecificacao.GroupBy(t => new { t.Conta, t.Tipo, t.Documento, t.Nome }) 
                           .Select(gp => new
                                   {
                                       Key = gp.Key,
                                       Qtd = gp.Count(),
                                       Itens = gp.ToList()
                                   });

The use would be something like:

foreach(var v in view)
{
    WriteLine(v.Qtd);

    foreach(var item in v.Itens)
    {
        WriteLine(item.Conta);
    }

    WriteLine();
}

See an example working on . NET Fiddle.

Complete code of the example:

using System;
using System.Collections.Generic;
using System.Linq;
using static System.Console;

public class Program
{
    public static void Main()
    {
        var view = TaxaPrecificacao
                     .GroupBy(t => new { t.Conta, t.Tipo, t.Documento, t.Nome }) 
                     .Select(gp => new
                             {
                                 Key = gp.Key,
                                 Qtd = gp.Count(),
                                 Itens = gp.ToList()
                             });

        foreach(var v in view)
        {
            WriteLine(v.Qtd);

            foreach(var item in v.Itens)
            {
                WriteLine($"{item.Conta} - {item.Valor}");
            }
            WriteLine();
        }           
    }

    static List<TaxaPrecificacao> TaxaPrecificacao = new List<TaxaPrecificacao>
    {
        new TaxaPrecificacao { Conta = "001", Tipo = 1, Documento = "A", 
                               Nome = "Tx 1", Valor = 5m },
        new TaxaPrecificacao { Conta = "001", Tipo = 1, Documento = "A", 
                               Nome = "Tx 1", Valor = 10m },
        new TaxaPrecificacao { Conta = "001", Tipo = 1, Documento = "A", 
                               Nome = "Tx 1", Valor = 52m },
        new TaxaPrecificacao { Conta = "002", Tipo = 2, Documento = "B", 
                               Nome = "Tx 2", Valor = 56m },
        new TaxaPrecificacao { Conta = "002", Tipo = 2, Documento = "B", 
                               Nome = "Tx 2", Valor = 59m },
        new TaxaPrecificacao { Conta = "002", Tipo = 2, Documento = "B", 
                               Nome = "Tx 2", Valor = 19m },
    };
}

class TaxaPrecificacao
{
    public string Conta { get; set; }
    public int Tipo { get; set; }
    public string Documento { get; set; }
    public string Nome { get; set; }
    public decimal Valor { get; set; }
}