How to Efficiently Translate a Complex SQL Query with Multiple Joins and Counts into LINQ?

SQL containing multiple connections, counting, and left connections to linq

SQL query

Consider the following SQL query:

<code class="language-sql">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</code>

linq query expression

To convert this SQL query to Linq query expression, please follow the following criteria:

Convert the query to the variables of separate declarations.
Convert each clause in order in order of linq clauses.
Convert Join clauses to navigation attributes or anonymous objects.
Use and
4. to simulate left join. GroupJoin DefaultIfEmpty()
Apply these rules to generate the following Linq query expression:

Lambda expression

<code class="language-csharp">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();</code>

For Lambda expressions conversion, further converts left join to

:

GroupJoin...SelectMany

The above is the detailed content of How to Efficiently Translate a Complex SQL Query with Multiple Joins and Counts into LINQ?. For more information, please follow other related articles on the PHP Chinese website!