View with Ienumerable and Entityframework

Question

I own a Sales entity and it has several fields (Iditem, Datavenda, Valorvenda, Taxadesconto, Idcliente, Idcategorito and etc).

I already have all Views working for this entity(CRUD).

Now I will create a new View in which Customers will be presented in a grouped form with their respective totals.

Questions:

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

2) In the database I can view 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?

Answer 1

The generation of a ViewModel (which would be a new class representing SQL). The die typical it is easier to use on a screen with MVC ASP.NET and also with ORM Entity Framework.

The Entity Framework quietly generates this SQL you only need to use the extension methods correctly (Where, Join, Groupby, Count no Select, etc) to generate that same SQL that you brought in your question or else use SQL Raw as this example.

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 it in your Context variable (Entity Framework):

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

}

Answer 2

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

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

2 - in the database I can view 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();

Recalling that the 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>