sql - query - system linq left join



SQL para LINQ com múltiplos join, count e left join (1)

Para traduzir o SQL para compreensão de consulta LINQ:

  1. Traduza subselects como variáveis ​​declaradas separadamente.
  2. Traduza cada cláusula na ordem da cláusula LINQ, traduzindo operadores monádicos e agregados ( DISTINCT , TOP , MIN , MAX , etc.) em funções aplicadas a toda a consulta LINQ.
  3. Use aliases de tabela como variáveis ​​de intervalo. Use aliases de coluna como nomes de campo de tipo anônimo.
  4. Use tipos anônimos ( new { ... } ) para várias colunas (por exemplo, em groupby ).
  5. Use o campo First().field para obter valores não-chave da variável de intervalo agregado groupby (por exemplo, como no MySQL).
  6. JOIN condições de JOIN que não são todos os testes de igualdade com AND devem ser manipuladas usando cláusulas where fora da junção, ou com produto cruzado ( from ... from ...) e, where seguida, where . Se você estiver fazendo LEFT JOIN , inclua uma cláusula lambda Where entre a variável de intervalo de junção e a chamada DefaultIfEmpty() .
  7. Condições de JOIN que são múltiplos testes de igualdade AND entre as duas tabelas devem ser convertidas em objetos anônimos
  8. LEFT JOIN é simulado usando joinvariable e fazendo outro a partir from joinvariable seguido de .DefaultIfEmpty() .
  9. Substitua COALESCE pelo operador condicional ( ?: COALESCE E um teste null .
  10. Traduza IN para .Contains() e NOT IN para ! ... Contains() , usando matrizes literais ou variáveis ​​de matriz para listas constantes.
  11. Tradutor x BETWEEN baixo AND alto para baixo <= x && x <= alto .
  12. Traduzir CASE para o operador condicional ternário ?: .
  13. SELECT * deve ser substituído por select range_variable ou por joins, um objeto anônimo contendo todas as variáveis ​​range.
  14. SELECT campos SELECT devem ser substituídos por select new { ... } criando um objeto anônimo com todos os campos ou expressões desejados.
  15. Traduza IIF para o operador condicional ternário C #.
  16. O FULL OUTER JOIN adequado deve ser tratado com um método de extensão.
  17. Traduza UNION para Concat menos que ambas as subconsultas sejam DISTINCT . DISTINCT caso, você poderá traduzir para o Union e deixar o DISTINCT .

Aplicando essas regras à sua consulta SQL, você obtém:

var subrq = from r in Table_R
            group r by r.Id into rg
            select new { Id = rg.Key, cnt = rg.Count() };

var ansq = (from c in Table_C
            join v in Table_V on c.Id equals v.Id
            join r in subrq on c.Id equals r.Id into rj
            from r in rj.DefaultIfEmpty()
            where c.IdUser == "1234"
            group new { c, v, r } by new { c.Id, c.Title, r.cnt } into cvrg
            select new {
                cvrg.Key.Title,
                Nb_V2 = cvrg.Count(),
                Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(),
                Nb_R = (int?)cvrg.Key.cnt
            }).Distinct();

A tradução lambda é complicada, mas a conversão de LEFT JOIN para GroupJoin ... SelectMany é o que é necessário:

var subr2 = Table_R.GroupBy(r => r.Id).Select(rg => new { Id = rg.Key, cnt = rg.Count() });
var ans2 = Table_C.Where(c => c.IdUser == "1234")
                  .Join(Table_V, c => c.Id, v => v.Id, (c, v) => new { c, v })
                  .GroupJoin(subr, cv => cv.c.Id, r => r.Id, (cv, rj) => new { cv.c, cv.v, rj })
                  .SelectMany(cvrj => cvrj.rj.DefaultIfEmpty(), (cvrj, r) => new { cvrj.c, cvrj.v, r })
                  .GroupBy(cvr => new { cvr.c.Id, cvr.c.Title, cvr.r.cnt })
                  .Select(cvrg => new { cvrg.Key.Title, Nb_V2 = cvrg.Count(), Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(), Nb_R = (int?)cvrg.Key.cnt });

Eu escrevi este pedido SQL com vários JOIN (incluindo um LEFT JOIN ).
Isso me dá o resultado esperado .

SELECT DISTINCT c.Id, 
       c.Title, 
       COUNT(v.Id) AS 'Nb_V2',
       COUNT(DISTINCT v.IdUser) AS 'Nb_V1',
       r.cnt AS 'Nb_R'
FROM TABLE_C c
JOIN TABLE_V v on c.Id = v.Id
LEFT JOIN ( 
    SELECT Id, COUNT(*)  AS cnt 
    FROM TABLE_R 
    GROUP BY Id
) r ON c.Id = r.Id
WHERE c.IdUser = '1234'
GROUP BY c.Id, c.Title, r.cnt

No entanto, 'Id como o equivalente de Linq deste pedido, para colocar a camada de acesso a dados do meu aplicativo.

Eu tentei algo como:

var qResult = from c in dbContext.TABLE_C
              join v in dbContext.TABLE_V on c.IdC equals v.IdC
              join r in dbContext.TABLE_R on v.IdC equals r.IdC into temp
              from x in temp.DefaultIfEmpty()
              group x by new { c.IdC, c.Title /*miss something ?*/} into grouped
              select new
              {
                  IdC = grouped.Key.IdC,          --good result
                  Title = grouped.Key.Title,      --good result
                  NbR = grouped.Distinct().Count(t => t.IdC > 0), --good, but "t.Id > 0" seems weird
                  Count = --I'm lost. No idea how to get my COUNT(...) properties (Nb_V1 and Nb_V2)
              };

Eu tentei adaptar essa pergunta, mas não consigo entender. Estou perdido com o Count dentro do sub-pedido agrupado.
Alguém pode me explicar onde eu estou errado?

Dica profissional: ponto de bônus se alguém puder escrever o equivalente com uma expressão lambda





sql-to-linq-conversion