none
Join and Group by/Order by RRS feed

  • Question

  • In the following query i need get some fields from the join statement, but i cant. whats wrong?
     
    var query = from p in persistencia.RequisicaoCompraItems
                            join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID into supI
                            group p by new {p.SuprimentoID, p.RequisicaoCompraItemID, p.RequisicaoCompraID } into x
                            from res in x
                            orderby x.Key.SuprimentoID
                            select new {res.SuprimentoID ,
                                        x.Key.RequisicaoCompraItemID };

    Thanks in advance.
    kaneda182
    Wednesday, November 11, 2009 12:05 PM

Answers

  • I dont know if this better way to do it.but work

          var query = from p in persistencia.RequisicaoCompraItems
                            join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID into supI
                            from supItems in supI
                            join r in persistencia.RequisicaoCompras on p.RequisicaoCompraID equals r.RequisicaoCompraID into reqI
                            from reqItem in reqI
                            group p by new {supItems.DescricaoInterna,
                                            p.SuprimentoID,
                                            p.RequisicaoCompraItemID,
                                            p.RequisicaoCompraID,
                                            reqItem.EmpresaID,
                                            reqItem.StatusRequisicao ,
                                            reqItem.FinanceiroEstruturaCustoID ,
                                            reqItem.DataSolicitacao,
                                            EmpresaNome = (from emp in persistencia.Empresas
                                                           where emp.EmpresaID == reqItem.EmpresaID
                                                           select emp.Nome).Single() ,
                                            EstruturaCusto = (from str in persistencia.FinanceiroEstruturaCustos
                                                              where str.FinanceiroEstruturaCustoID == reqItem.FinanceiroEstruturaCustoID
                                                              select str.Descricao).Single(),
                                             } into x
                            from res in x
                            orderby x.Key.SuprimentoID
                            select new {res.SuprimentoID ,
                                        x.Key.RequisicaoCompraItemID ,
                                        x.Key.DescricaoInterna ,
                                        x.Key.FinanceiroEstruturaCustoID ,
                                        x.Key.EmpresaID ,
                                        x.Key.RequisicaoCompraID,
                                        Status = (x.Key.StatusRequisicao == 0 ? "Cadastrado" :
                                                     x.Key.StatusRequisicao == 1 ? "Conferido" :
                                                     x.Key.StatusRequisicao == 2 ? "Liberado" :
                                                     x.Key.StatusRequisicao == 3 ? "Em cotação" :
                                                     x.Key.StatusRequisicao == 4 ? "Cotado" :
                                                     x.Key.StatusRequisicao == 5 ? "Compra liberada" :
                                                     x.Key.StatusRequisicao == 6 ? "Em compra" :
                                                     x.Key.StatusRequisicao == 7 ? "Compra efetuada" :
                                                     x.Key.StatusRequisicao == 8 ? "Entrega parcial" :
                                                     x.Key.StatusRequisicao == 8 ? "Entrega total" :
                                                     x.Key.StatusRequisicao == 10 ? "Cancelado" :
                                                     x.Key.StatusRequisicao == 11 ? "Suspenso" :
                                                     x.Key.StatusRequisicao == 12 ? "Compra não autorizada" : "Indeterminado"),
                            };
    kaneda182
    • Marked as answer by kaneda182 Wednesday, November 11, 2009 2:17 PM
    Wednesday, November 11, 2009 2:16 PM
  • In the following query i need get some fields from the join statement, but i cant. whats wrong?
     
    var query = from p in persistencia.RequisicaoCompraItems
                            join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID into supI
                            group p by new {p.SuprimentoID, p.RequisicaoCompraItemID, p.RequisicaoCompraID } into x
                            from res in x
                            orderby x.Key.SuprimentoID
                            select new {res.SuprimentoID ,
                                        x.Key.RequisicaoCompraItemID };

    Thanks in advance.
    kaneda182

    The group join variable supI goes out of scope after the group clause.  Group and select clauses end queries unless they are continued with the into clause, like you are using.  But only the into variable is carried forward, so any data you want to reference must be accessible from this variable.

    You could simply do it like this:

    var query = from p in persistencia.RequisicaoCompraItems
                            join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID into supI
                            group new {p, supI} by new {p.SuprimentoID, p.RequisicaoCompraItemID, p.RequisicaoCompraID } into x
                            from res in x
                            orderby x.Key.SuprimentoID
                            select new {res.p.SuprimentoID ,
                                        x.Key.RequisicaoCompraItemID,
                                        res.supI.xxx };

    Or since you are doing a group-join, you could simply move the join later in the query, after the 'from res in x'.


    Wayward LINQ Lacky
    • Marked as answer by kaneda182 Wednesday, November 11, 2009 8:45 PM
    Wednesday, November 11, 2009 8:14 PM
    Moderator

All replies

  • I dont know if this better way to do it.but work

          var query = from p in persistencia.RequisicaoCompraItems
                            join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID into supI
                            from supItems in supI
                            join r in persistencia.RequisicaoCompras on p.RequisicaoCompraID equals r.RequisicaoCompraID into reqI
                            from reqItem in reqI
                            group p by new {supItems.DescricaoInterna,
                                            p.SuprimentoID,
                                            p.RequisicaoCompraItemID,
                                            p.RequisicaoCompraID,
                                            reqItem.EmpresaID,
                                            reqItem.StatusRequisicao ,
                                            reqItem.FinanceiroEstruturaCustoID ,
                                            reqItem.DataSolicitacao,
                                            EmpresaNome = (from emp in persistencia.Empresas
                                                           where emp.EmpresaID == reqItem.EmpresaID
                                                           select emp.Nome).Single() ,
                                            EstruturaCusto = (from str in persistencia.FinanceiroEstruturaCustos
                                                              where str.FinanceiroEstruturaCustoID == reqItem.FinanceiroEstruturaCustoID
                                                              select str.Descricao).Single(),
                                             } into x
                            from res in x
                            orderby x.Key.SuprimentoID
                            select new {res.SuprimentoID ,
                                        x.Key.RequisicaoCompraItemID ,
                                        x.Key.DescricaoInterna ,
                                        x.Key.FinanceiroEstruturaCustoID ,
                                        x.Key.EmpresaID ,
                                        x.Key.RequisicaoCompraID,
                                        Status = (x.Key.StatusRequisicao == 0 ? "Cadastrado" :
                                                     x.Key.StatusRequisicao == 1 ? "Conferido" :
                                                     x.Key.StatusRequisicao == 2 ? "Liberado" :
                                                     x.Key.StatusRequisicao == 3 ? "Em cotação" :
                                                     x.Key.StatusRequisicao == 4 ? "Cotado" :
                                                     x.Key.StatusRequisicao == 5 ? "Compra liberada" :
                                                     x.Key.StatusRequisicao == 6 ? "Em compra" :
                                                     x.Key.StatusRequisicao == 7 ? "Compra efetuada" :
                                                     x.Key.StatusRequisicao == 8 ? "Entrega parcial" :
                                                     x.Key.StatusRequisicao == 8 ? "Entrega total" :
                                                     x.Key.StatusRequisicao == 10 ? "Cancelado" :
                                                     x.Key.StatusRequisicao == 11 ? "Suspenso" :
                                                     x.Key.StatusRequisicao == 12 ? "Compra não autorizada" : "Indeterminado"),
                            };
    kaneda182
    • Marked as answer by kaneda182 Wednesday, November 11, 2009 2:17 PM
    Wednesday, November 11, 2009 2:16 PM
  • In the following query i need get some fields from the join statement, but i cant. whats wrong?
     
    var query = from p in persistencia.RequisicaoCompraItems
                            join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID into supI
                            group p by new {p.SuprimentoID, p.RequisicaoCompraItemID, p.RequisicaoCompraID } into x
                            from res in x
                            orderby x.Key.SuprimentoID
                            select new {res.SuprimentoID ,
                                        x.Key.RequisicaoCompraItemID };

    Thanks in advance.
    kaneda182

    The group join variable supI goes out of scope after the group clause.  Group and select clauses end queries unless they are continued with the into clause, like you are using.  But only the into variable is carried forward, so any data you want to reference must be accessible from this variable.

    You could simply do it like this:

    var query = from p in persistencia.RequisicaoCompraItems
                            join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID into supI
                            group new {p, supI} by new {p.SuprimentoID, p.RequisicaoCompraItemID, p.RequisicaoCompraID } into x
                            from res in x
                            orderby x.Key.SuprimentoID
                            select new {res.p.SuprimentoID ,
                                        x.Key.RequisicaoCompraItemID,
                                        res.supI.xxx };

    Or since you are doing a group-join, you could simply move the join later in the query, after the 'from res in x'.


    Wayward LINQ Lacky
    • Marked as answer by kaneda182 Wednesday, November 11, 2009 8:45 PM
    Wednesday, November 11, 2009 8:14 PM
    Moderator