View with IEnumerable and EntityFramework

It will be very important to generate a ViewModel (which would be a new class representing SQL ). The typed data is simpler to use on a screen with ASP.NET MVC and also ORM Entity Framework .

Example:

ViewModel

public class DadosViewModel
{        
    public int IdCliente { get; set; }
    public string NomeCliente { get; set; }
    public decimal ValorVenda { get; set; }
    public long TotalItems { get; set; }
}

SQL Raw

You can use this in your Context (Entity Framework) variable:

 string SQL = " select b.IdCliente, b.NomeCliente, Sum(ValorVenda) as  ValorVenda, ";
 SQL += " Count(*) as TotalItens from TabVendas a inner join TabCliente b ";
 SQL += " on a.IdCliente = b.IdCliente";

 IList<DadosViewModel> ListaDadosViewModel = Contexto.Database
                                              .SqlQuery<DadosViewModel>(SQL).ToList();

SQL by the Entity Framework

IList<DadosViewModel> ListaDadosViewModel = Contexto
         .Vendas
         .GroupBy(c => c.Cliente)
         .Select(s => new DadosViewModel {
            IdCliente = s.Key.IdCliente,
            NomeCliente = s.Key.NomeCliente, 
            ValorVenda = s.Sum(v => v.ValorVenda),
            TotalItems = s.LongCount()
         })
         .toList();

View

@model IEnumerable<ViewModels.DadosViewModel>

@foreach(ViewModels.DadosViewModel item in Model)
{

}

  

1 - Do I need to create a specific Model to represent this View?

Not necessarily. The grouping and totalizing functions can be done in View , if you want.

  

2 - In the database I can see this information using the following query:

select b.IdCliente, b.NomeCliente, Sum(ValorVenda) as ValorVenda, Count() as TotalItens 
from TabVendas a 
inner join TabCliente b on a.IdCliente = b.IdCliente 

  

How to do this same query via EntityFramework?

var totalVendas = db.Vendas.Include(v => v.Cliente)
                    .GroupBy(v => v.Cliente)
                    .Select(g => new {
                        IdCliente = g.Key.IdCliente,
                        NomeCliente = g.Key.NomeCliente
                        ValorTotalVenda = g.Sum(v => v.ValorVenda),
                        TotalItens = g.Count()
                    })
                    .ToList();

Remembering View should receive this as:

@model IEnumerable<dynamic>

Or you can type the return object as ViewModel :

var totalVendas = db.Vendas.Include(v => v.Cliente)
                    .GroupBy(v => v.Cliente)
                    .Select(g => new RelatorioVendasItemViewModel {
                        IdCliente = g.Key.IdCliente,
                        NomeCliente = g.Key.NomeCliente
                        ValorTotalVenda = g.Sum(v => v.ValorVenda),
                        TotalItens = g.Count()
                    })
                    .ToList();

And then:

@model IEnumerable<SeuProjeto.ViewModels.RelatorioVendasItemViewModel>