SQL query for Linq mvc4 c#

Asked

Viewed 100 times

2

I’m not very familiar with Linq and I’m having a hard time turning this SQL query into Linq, can someone help me.

I do the join of 6 tables (it wasn’t me who made the database. I have to work with it), what I’m holding is in the group by and in the having

select processosProduto.PRODUTO,SUM(pedidosVenda1.QUANT) as vendidos ,processosProduto.QUANT as estoque, processoFinanceiro.CODIGO, processoFinanceiro.ProdutosContidos
from processoFinanceiro 
join processosProduto on processoFinanceiro.CODIGO = processosProduto.CODIGO
join OrdemDeCompra on processosProduto.PRODUTO = OrdemDeCompra.Codigo
join produtos on OrdemDeCompra.PARALELO = produtos.CODIGO
join pedidosVenda1 on produtos.CODIGO = pedidosVenda1.PRODUTO
join pedidosVenda on pedidosVenda1.CODIGO = pedidosVenda.CODIGO
where pedidosVenda.NPedido = processoFinanceiro.NPedidoFornecedor 
group by processosProduto.PRODUTO,processosProduto.QUANT,  processoFinanceiro.CODIGO, processoFinanceiro.ProdutosContidos
having SUM(pedidosVenda1.QUANT) < processosProduto.QUANT*120/100

EDITED

Here’s what I’ve been able to do so far

var listaDb = (from pf in db.processoFinanceiro
                           join pp in db.processosProduto on pf.CODIGO equals pp.CODIGO
                           join oc in db.OrdemDeCompras on pp.PRODUTO equals oc.PARALELO
                           join pr in db.produtos on oc.PARALELO equals pr.CODIGO
                           join pv1 in db.pedidosVenda1 on pr.CODIGO equals pv1.PRODUTO
                           join pv in db.pedidosVenda on pv1.CODIGO equals pv.CODIGO
                           where pv.NPedido == pf.NPedidoFornecedor

but I’m not managing to develop any more than that point, as I said what I’m holding on to is the group by and in the having

  • Post your model. and what have you tried to do.

  • I edited the publication with the requested information. I know I have to close the parentheses, and there is more code missing, like select, but I need to develop having and group by before proceeding ( I think)

2 answers

2

I made it up here It must be something like this:

(from pf in processoFinanceiro
            join pp in processosProduto on pf.CODIGO equals pp.CODIGO
            join oc in OrdemDeCompras on pp.PRODUTO equals oc.PARALELO
            join pr in produtos on oc.PARALELO equals pr.CODIGO
            join pv1 in pedidosVenda1 on pr.CODIGO equals pv1.PRODUTO
            join pv in pedidosVenda on pv1.CODIGO equals pv.CODIGO
            group new {pp.PRODUTO, pp.QUANT, pvQUANT = pv1.QUANT, pf.CODIGO, pf.ProdutosContidos}
            by new {pp.PRODUTO, pp.QUANT, pf.CODIGO, pf.ProdutosContidos}
            into grp               
            select new
            {
                grp.Key.PRODUTO,
                vendidos = grp.Sum(b=>b.pvQUANT),
                estoque = grp.Key.QUANT,
                grp.Key.CODIGO,
                grp.Key.ProdutosContidos
            });

in the Group new {....} you place the properties you will use to return and on by new {...} you place the properties you want to group

ex: if you want to do SUM, you cannot put this property in by because it will only group the properties that are equal.

REFERENCE

  • I think his query is very close to what he needs, just lacked the having filter.

  • Thank you very much @Marconciliosouza! through this code I was able to perform the search, was missing a Where ' Where Pv.Npedido == pf.Npedidosupplier' and having I can do upon the search result

  • I have to give a code groupby and select code and Products , but I get an error of trying to convert a Iqueriable<Anonimoustype#4> to a Iqueriable<Anonimoustype#2> you know what this could be? query = (from c in query Where c.stock > c.sellers group new { c.CODIGO, c.Productscontains } by new { c.CODIGO} into gr select new { gr.Key.CODE, gr.Key.Productscontacts });

1


Without the model to do a test get a little difficult, but see how it would work group by and the having in the Ligurian.

Practically the having becomes a where after the group by.

var listaDb = (from pf in db.processoFinanceiro
               join pp in db.processosProduto on pf.CODIGO equals pp.CODIGO
               join oc in db.OrdemDeCompras on pp.PRODUTO equals oc.PARALELO
               join pr in db.produtos on oc.PARALELO equals pr.CODIGO
               join pv1 in db.pedidosVenda1 on pr.CODIGO equals pv1.PRODUTO
               join pv in db.pedidosVenda on pv1.CODIGO equals pv.CODIGO
               where pv.NPedido == pf.NPedidoFornecedor
               group pv by new { pp.PRODUTO, pf.CODIGO, pf.ProdutosContidos } into g
               where g.Sum(pp => pp.QUANT) < (g.Sum(pp => pp.QUANT) *120/100)
               select new 
               { 
                    PRODUTO = g.Key.PRODUTO, 
                    CODIGO = g.Key.CODIGO, 
                    ProdutosContidos = g.Key.ProdutosContidos,
                    QUANT = g.Sum(pp => pp.QUANT) 
                });

Browser other questions tagged

You are not signed in. Login or sign up in order to post.