SQL a LINQ con combinación múltiple, recuento y combinación izquierda

join left-join

Question

Escribí esta solicitud SQL con múltiples JOIN (incluyendo una LEFT JOIN ).
Me da el 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

Sin embargo, ''me gustaría el equivalente de Linq de esta solicitud, para ponerlo en la capa de acceso a datos de mi aplicación.

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

Traté de adaptar esta pregunta SO pero no puedo resolverlo. Estoy perdido con el Count dentro de la sub-solicitud agrupada.
¿Alguien puede explicarme dónde estoy equivocado?

Consejo profesional: punto extra si alguien puede escribir el equivalente con una expresión lambda

Answer 1

Para traducir la comprensión de consultas SQL a LINQ:

1. Traducir subselecciones como variables declaradas por separado.
2. Traduzca cada cláusula en orden de cláusula LINQ, traduciendo operadores monádicos y agregados ( DISTINCT , TOP , MIN , MAX , etc.) en funciones aplicadas a toda la consulta LINQ.
3. Use alias de tabla como variables de rango. Use alias de columna como nombres de campo de tipo anónimo.
4. Utilice tipos anónimos ( new { ... } ) para varias columnas (por ejemplo, en groupby ).
5. Utilice el campo First().field Para obtener valores no clave de la variable de rango agregado groupby (por ejemplo, como con MySQL).
6. JOIN condiciones de JOIN que no son todas pruebas de igualdad con AND deben manejarse utilizando cláusulas where fuera de la unión, o con producto cruzado ( from ... from ...) y luego where . Si está haciendo LEFT JOIN , agregue una cláusula lambda Where entre la variable de rango de combinación y la llamada DefaultIfEmpty() .
7. JOIN condiciones de JOIN que son pruebas de igualdad múltiples AND ed entre las dos tablas deben traducirse en objetos anónimos
8. LEFT JOIN se simula usando into joinvariable y haciendo otro desde joinvariable seguido de .DefaultIfEmpty() .
9. Reemplace COALESCE con el operador condicional ( ?: COALESCE Y una prueba null .
10. Traduce IN a .Contains() y NOT IN a ! ... Contains() , utilizando matrices literales o variables de matriz para listas constantes.
11. Traducir x BETWEEN bajo AND alto a bajo <= x && x <= alto .
12. Traducir CASE al operador condicional ternario ?: .
13. SELECT * debe reemplazarse con select range_variable o para uniones, un objeto anónimo que contiene todas las variables de rango.
14. SELECT campos SELECT deben reemplazarse con select new { ... } creando un objeto anónimo con todos los campos o expresiones deseados.
15. Traduzca IIF al operador condicional ternario C #.
16. La FULL OUTER JOIN adecuada debe manejarse con un método de extensión.
17. Traduzca UNION a Concat menos que ambas Concat sean DISTINCT , en cuyo caso puede traducir a Union y dejar el DISTINCT .

Aplicando estas reglas a su consulta SQL, obtiene:

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

La traducción lambda es complicada, pero la conversión de LEFT JOIN a GroupJoin ... SelectMany es lo que se necesita:

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