none
Where IN, Group By, Having Count >= Nested Query RRS feed

  • Question

  • Hi again... I'm struggling on translating sql queries to linq queries. The problem right now is GROUP BY HAVING COUNT >= NESTED QUERY. Look what I mean in the sql query below:

    SELECT CodigoProduto
             FROM dbo.lstProdutosRepresentantes
             WHERE CodigoColaborador IN
                     (
                         SELECT CodigoColaborador
                                 FROM lstCarteiras
                                  WHERE CodigoConta = 10
                     )
             GROUP BY CodigoProduto
             HAVING COUNT(CodigoProduto) >= (SELECT COUNT(*) FROM lstCarteiras WHERE CodigoConta = 10
    


    I couldn't get the GROUP BY clause done. I managed to write the IN clause:

    from produtoRepresentante in this.Context.ProdutosRepresentantes
    //group produtoRepresentante by produtoRepresentante.Produto.CodigoProduto into gp
    where
    (from carteira in this.Context.Carteiras
     where carteira.Conta.CodigoConta == codigoConta
     select carteira.Colaboradore.CodigoColaborador)
      .Contains(produtoRepresentante.Colaboradore.CodigoColaborador)
    select produtoRepresentante.Produto.CodigoProduto;
    
    

    Any help?

     

    Monday, November 14, 2011 1:28 PM

Answers

  • Hi Joberto,

    Please refer here, I think this case can help you. If you have any problem, please feel free to let me know.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Joba Diniz Thursday, November 17, 2011 10:22 AM
    Wednesday, November 16, 2011 5:21 AM
    Moderator
  • (from produtoRepresentante in this.Context.ProdutosRepresentantes
                         where
                         (from carteira in this.Context.Carteiras
                          where carteira.CodigoConta == codigoConta
                          select carteira.CodigoColaborador)
                           .Contains(produtoRepresentante.CodigoColaborador) //where first
                         group produtoRepresentante by produtoRepresentante.CodigoProduto into gp //group by after
                         where gp.Count() >=
                         (from carteira in this.Context.Carteiras
                          where carteira.CodigoConta == codigoConta
                          select carteira.CodigoCarteira).Count()
                         select gp.Key)
                        .Contains(produto.CodigoProduto)
                        select produto;
    
    I managed to do like above. However, as you can see, first I wrote the WHERE clause and only after I wrote the GROUPBY clause. I'm not entirely sure if it is right, I mean, does it matter whether I write the where or groupby first?

     

    • Marked as answer by Joba Diniz Thursday, November 17, 2011 10:22 AM
    Wednesday, November 16, 2011 10:25 AM

All replies

  • Hi Joberto,

    Please refer here, I think this case can help you. If you have any problem, please feel free to let me know.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Joba Diniz Thursday, November 17, 2011 10:22 AM
    Wednesday, November 16, 2011 5:21 AM
    Moderator
  • (from produtoRepresentante in this.Context.ProdutosRepresentantes
                         where
                         (from carteira in this.Context.Carteiras
                          where carteira.CodigoConta == codigoConta
                          select carteira.CodigoColaborador)
                           .Contains(produtoRepresentante.CodigoColaborador) //where first
                         group produtoRepresentante by produtoRepresentante.CodigoProduto into gp //group by after
                         where gp.Count() >=
                         (from carteira in this.Context.Carteiras
                          where carteira.CodigoConta == codigoConta
                          select carteira.CodigoCarteira).Count()
                         select gp.Key)
                        .Contains(produto.CodigoProduto)
                        select produto;
    
    I managed to do like above. However, as you can see, first I wrote the WHERE clause and only after I wrote the GROUPBY clause. I'm not entirely sure if it is right, I mean, does it matter whether I write the where or groupby first?

     

    • Marked as answer by Joba Diniz Thursday, November 17, 2011 10:22 AM
    Wednesday, November 16, 2011 10:25 AM
  • Hi Joberto,

    If you write the where statement first, it means you filter the record in the collection first, then group the quanlified record. But if you write the where statement after gourp by, it means you group by the record first and then filter the record.

    If you have 100 records in the collection, write where statement first, and 80 records qualified, group by statement will group the 80 records. If you write the group by statement first, it will group by all 100 records and then filter the qualified records from the 100 records.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, November 17, 2011 2:44 AM
    Moderator
  • So... in my case: doesn't matter. :D
    Thursday, November 17, 2011 10:22 AM