Consultation Group by Linq

Question

It would be possible to consult the table below in a single query?

It would be a group by query using Context with Entity Framework where:

The 3 customers who spent the most on services per month in the current year according to table :

The tables would be:

-client table: Name; Neighborhood; City; State;

namespace ServicosPrestados.Models
{
    public class Cliente
    {
        public int Id { get; set; }

        public string Nome { get; set; }

        public string Bairro { get; set; }

        public string Cidade { get; set; }

        public string Estado { get; set; }
    }
}

-Table of services provided: Description of service Call date Service value

namespace ServicosPrestados.Models.Entidades
{
    public class Servico
    {
        public int Id { get; set; }
        public string Descricao { get; set; }
        public DateTime Data { get; set; }
        public decimal Valor { get; set; }
        public virtual Cliente Cliente { get; set; }
        public int IdCliente { get; set; }

    }
}

mapping:

using ServicosPrestados.Models.Entidades;
using System.Data.Entity.ModelConfiguration;

namespace ServicosPrestados.Models.Mapeamento
{
    public class ServicoMapeamento : EntityTypeConfiguration<Servico>
    {
        public ServicoMapeamento()
        {
            ToTable("SERVICO");

            HasKey(x => x.Id);

            Property(x => x.IdCliente)
               .HasColumnName("ID_CLIENTE")
               .HasColumnType("int")
               .IsRequired();

            Property(x => x.Descricao)
               .HasColumnName("DESCRICAO")
               .HasColumnType("varchar")
               .HasMaxLength(100)
               .IsRequired();

            Property(x => x.Data)
               .HasColumnName("DATA")
               .HasColumnType("datetime")
               .IsRequired();

            Property(x => x.Valor)
              .HasColumnName("VALOR")
              .HasColumnType("decimal")
              .IsRequired();

            HasRequired(m => m.Cliente).WithMany().HasForeignKey(m => m.IdCliente);
        }
    }
}

Answer 1

There’s not much mystery, just use the method GroupBy.

Note that I created an instance of CultureInfo to have control over the name of the months, in your case you need to see how the application handles this.

var cultura = new CultureInfo("pt-BR"); 
contexto.Servicos.GroupBy(serv => serv.Data.Month)
                  .Select(gp => new
                         {
                             Mes = cultura.DateTimeFormat.GetMonthName(gp.Key),
                             Servicos = gp.GroupBy(s => s.Cliente)
                                          .Select(g => new
                                                       { 
                                                           Cliente = g.Key, 
                                                           Valor = g.Sum(x => x.Valor)  
                                                       })
                          });

If you want to take a test, here’s a functional code

using System;
using System.Linq;
using System.Globalization;
using System.Collections.Generic;

class MainClass 
{
    static Cliente[] clientes = new [] 
    {
        new Cliente { Nome = "1 - Abel" },
        new Cliente { Nome = "2 - Bruna" },
        new Cliente { Nome = "3 - Carlos" },
        new Cliente { Nome = "4 - Dara" },
        new Cliente { Nome = "8 - Helena" }
    };

    static IEnumerable<Servico> servicos = new [] 
    {
        new Servico
        {
            Data = new DateTime(2019, 01, 02),
            Valor = 100,
            Cliente = clientes[0]
        },
        new Servico
        {
            Data = new DateTime(2019, 01, 13),
            Valor = 200,
            Cliente = clientes[0]
        },
        new Servico
        {
            Data = new DateTime(2019, 01, 13),
            Valor = 123,
            Cliente = clientes[1]
        },
        new Servico
        {
            Data = new DateTime(2019, 01, 13),
            Valor = 324,
            Cliente = clientes[1]
        },
        new Servico
        {
            Data = new DateTime(2019, 01, 13),
            Valor = 543,
            Cliente = clientes[2]
        },
        new Servico
        {
            Data = new DateTime(2019, 02, 21),
            Valor = 421,
            Cliente = clientes[2]
        },
        new Servico
        {
            Data = new DateTime(2019, 02, 21),
            Valor = 421,
            Cliente = clientes[3]
        },
        new Servico
        {
            Data = new DateTime(2019, 02, 21),
            Valor = 421,
            Cliente = clientes[4]
        }
    };

    public static void Main (string[] args) 
    {
        var cultura = new CultureInfo("pt-BR");
        var resultado = servicos
                            .GroupBy(serv => serv.Data.Month)
                            .Select(gp => new
                                   {
                                       Mes = cultura.DateTimeFormat.GetMonthName(gp.Key),
                                       Servicos = gp.GroupBy(s => s.Cliente)
                                                    .Select(g => new 
                                                                 { 
                                                                   Cliente = g.Key, 
                                                                   Valor = g.Sum(x => x.Valor)
                                                                 })
                                   });

        foreach(var item in resultado)
        {
             Console.WriteLine(item.Mes);
             foreach(var servico in item.Servicos)
             {
                Console.WriteLine($"\t\t{servico.Cliente.Nome} - {servico.Valor}");
             }
        }

    }
}

public class Servico
{
    public DateTime Data { get; set; }
    public decimal Valor { get; set; }
    public Cliente Cliente { get; set; }
    public int IdCliente { get; set; }
}

public class Cliente
{
    public string Nome { get; set; }
}

^{See working on Repl.it}