How to perform a Count in several columns?

Question

How to perform a Count in several columns?

Navigation

#1 by (4 votes)

3

I would like to make a COUNT using LINQ / LAMBDA, but I'm having difficulties. Can you 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

Here are some ways I tried to do it, but of course all are 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()
});

c# linq

asked by anonymous 27.09.2017 / 17:41

1 answer

Javascript: How to insert multiple blanks into a paragraph p element? Fade effect with CSS3

score 4 · Accepted Answer

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

This structure contains all the data of a given grouping and also the key ( Key ) of 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 the data of its grouping and a member Key that will also be a structure with all members of the grouping, something like:

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

Then, after using the GroupBy() method, you can work on this structure and use the Count() method to return the number of elements in each grouping, or Sum() to sum 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 usage would look something like:

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

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

    WriteLine();
}

See an example working in .NET Fiddle.

Full example code:

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