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

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 access 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 SO, mas não consigo entender. Estou perdido com o Count dentro do sub-pedido agrupado.
Alguém pode me explicar onde estou errado?

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

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

  1. Traduza subselects como variables ​​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 variables ​​de intervalo. Use aliases de coluna como nomes de campo de tipo anônimo.
  4. Use tipos anônimos ( new {} ) para várias colunas.
  5. 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 junit, ou com produto cruzado ( fromfrom …) e, where seguida, where . Se você estiver fazendo LEFT JOIN , adicione uma cláusula lambda Where entre a variável de intervalo de junit e a chamada DefaultIfEmpty() .
  6. Condições de JOIN que são vários testes de igualdade AND entre as duas tabelas devem ser convertidas em objects anônimos
  7. LEFT JOIN é simulado usando joinvariable e fazendo outro a partir from joinvariable seguida de .DefaultIfEmpty() .
  8. Substitua COALESCE pelo operador condicional ( ?: COALESCE E um teste null .
  9. Traduza IN para .Contains() e NOT IN para !Contains() , usando matrizes literais ou variables ​​de matriz para listas constantes.
  10. Tradutor x BETWEEN baixo AND alto para baixo <= x && x <= alto .
  11. Traduzir CASE para o operador condicional ternário ?: .
  12. SELECT * deve ser substituído por select range_variable ou por joins, um object anônimo contendo todas as variables ​​de intervalo.
  13. SELECT campos SELECT devem ser substituídos por select new { ... } criando um object anônimo com todos os campos ou expressões desejados.
  14. O FULL OUTER JOIN adequado deve ser tratado com um método de extensão.

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 });