SQL to LINQ with multiple join, count and left join

Question

I wrote this SQL request with multiple JOIN (including a LEFT JOIN).
It gives me the expected result.

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

However, 'Id like the Linq equivalent of this request, to put it my application's Data Access layer.

I tried something like :

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

I tried to adapt this SO question but I can't figure it out. I'm lost with the Count inside the groupped sub-request.
Can anyone explain me where i'm wrong ?

Pro tip : Bonus point if someone can write the equivalent with a lambda expression

Answer 1

For translating SQL to LINQ query comprehension:

1. Translate subselects as separately declared variables unless they reference columns outside the subselect, in which case use parentheses to create a sub-query.
2. Translate each clause in LINQ clause order, translating single monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
3. Use table aliases as range variables. Use column aliases as anonymous type field names.
4. Use anonymous types (new { ... }) for multiple columns (e.g. in groupby).
5. Use First().field to get non-key values from the groupby aggregate range variable.
6. When using EF or EF Core, translate JOIN clauses into navigation properties possibly using .Include().
7. Otherwise JOIN clauses that are multiple ANDed equality tests between the two tables should be translated into anonymous objects on each side of equals.
8. JOIN conditions that aren't all equality tests with AND must be handled using where clauses outside the join, or with cross product (from ... from ...) and then where. If you are doing LEFT JOIN, add a lambda Where clause between the join range variable and the DefaultIfEmpty() call.
9. LEFT JOIN is simulated by using into joinvariable and doing another from the joinvariable followed by .DefaultIfEmpty().
10. Translate multiple tables in the FROM clause into multiple from clauses.
11. Translate FROM T1 CROSS APPLY T2 into two from clauses, one for T1 and one for T2.
12. Translate FROM T1 OUTER APPLY T2 into two from clauses, one for T1 and one for T2, but add .DefaultIfEmpty() to T2.
13. Replace COALESCE with the conditional operator (?:)and a null test.
14. Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
15. Translate x BETWEEN low AND high to low <= x && x <= high.
16. Translate CASE, ISNULL and IIF to the ternary conditional operator ?:.
17. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
18. SELECT columns must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
19. References to computed SELECT columns can be translated by repeating the expression or by using let to name the expression before its first use.
20. Proper FULL OUTER JOIN must be handled with an extension method.
21. Translate UNION to Concat unless both sub-queries are DISTINCT, in which case you can translate to Union and leave off the DISTINCT.
22. Translate aggregate queries with multiple result columns that have no GROUP BY using a singleton GroupBy: add .GroupBy(r => 1) (or group...by 1 into g) and then translate the aggregate functions in the Select new { }.
23. Date Math and some other canonical functions can be accessed using EF.Functions to get an instance of the DbFunctions class (EF Core), EntityFunctions class (EF < 6) or DbFunctions to access the static methods (EntityFramework 6.x).
24. Translate SQL LIKE expressions using (EF Core >= 2) EF.Functions.Like(column, pattern) or (EF 6.x) DbFunctions.Like(column, pattern).

Applying these rules to your SQL query, you get:

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

The lambda translation is tricky, but the conversion of LEFT JOIN to GroupJoin...SelectMany is what is needed:

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