How to generate a subquery in Line/EF

Asked

Viewed 578 times

4

Hello, everybody.

I have a situation, where I concatenate some subquerys Sqls that are concatenated into other Sqls queries, to return some values.

I have several functions like these, because I use them in several other queries. So I don’t need to repeat code.

I would like to know how I can do this using Linqsql + EF.

Follow the example of a simple code.

    public void Disponivel()
    {
        string query = "Select prod.Cod, prod.Descricao, ";
        query += QueryEstoqueDisponivel("prod.Cod") + " as EstoqueDiponivel ";
        query += " From TAB_Produtos prod ";

        //Aqui será gerado uma lista dos produtos...
    }


    public string QueryEstoqueDisponivel(string aliasProduto = "prod.Cod")
    {
        string query = "((Select Sum(est.Quantidade) " +
                       "  From ProdutoEstoque est " +
                       "   Where est.CodProdutos = " + aliasProduto +
                       " ) " +
                       " - (Select Sum(it.Quantidade) " +
                          " From PedidosItens it " +
                          " Where it.CodProdutos = " + aliasProduto + 
                          ")" +
                       ") ";

        return query;
    }
sql-server entity-framework linq linq-to-entities linq-to-sql
  • 1

    I can only give you a hint, your code is extraordinarily insecure. Rethink this.

  • The above code is just an example we need to do with Ingl. What do you mean by insecure?

  • That even a child will invade whatever you’re doing there.

  • What version of C# are you using? You can use string interpolation in the query = $"" and pass the other parameters, query = $"select * from {substring1} from {substring2}"

  • As I asked above, I need to do....

1 answer

1

Using the Entityframework, there will be in your project a context class for access to the database, and for each table (Tab_products, Productstoque e Pedidositens) there will be a class that represents it.

Probably your query would look like this: 

var lista = (from prod in contexto.TAB_Produtos 
             join est in contexto.ProdutoEstoque on prod.cod equals est.CodProduto
             join it in contexto.PedidosItens on prod.cod equals it.CodProduto
             group new 
             { 
                 CodProduto = prod.cod, 
                 Descricao = prod.Descricao,
                 QuantidadeEstoque = est.Quantidade, 
                 QuantidadePedidos = it.Quantidade 
             } 
             by new { prod.cod, prod.Descricao } 
             into g
             select new 
             {
                 CodProduto = g.key.cod,
                 Descricao = g.key.Descricao,
                 EstoqueDiponivel = g.Sum(ed => ed.QuantidadeEstoque) + g.Sum(ed.QuantidadePedidos)
             }).ToList();

Stay tuned to your SQL code construction, because the example you presented is very bad. Study this question, it is important.

  • We already think and do this way, however in our system has more than 100 reports or test, which needs to know the available stock of a product. How You Did Stock = g.Sum(ed => ed.Quantitythings) + g.Sum(ed.Quantityjobs), we would have to repeat this command on all calls.. If something is changed in the calculation, I have to go over the whole system.. Today we change only one function and the whole system is correct. For this reason I would like to know how to do the same example in Brazilian without alternative solutions.

  • @Fabianorichetti you are looking for the solution in the wrong place, the correct is you separate it and create a service that makes the inventory query and return an object with the data. And in the views or reports you consume this service and the other reporting service and return a specific view object with the dynamic list formatted the way you need it. Making the interpolation equal you want to make sql with Entity is not the way. Another way is to run sql by Entity and map to the required object list. Follow one of these paths.

Browser other questions tagged sql-server entity-framework linq linq-to-entities linq-to-sql

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