How to Convert a Complex SQL Query with Multiple Joins, Counts, and Left Joins into LINQ?

Convert complex SQL queries containing multiple joins, counts and left joins to LINQ

This example demonstrates how to convert a complex SQL query containing multiple joins and aggregations into an equivalent LINQ query. This query retrieves data from three tables (TABLE_C, TABLE_V, and TABLE_R) and performs a distinct count operation on specific columns.

Convert SQL to LINQ query understanding:

1. Group subqueries: Convert subqueries into individually declared variables. In this example, TABLE_R is grouped using anonymous types of Id and Count.
2. Join clause: Use navigation properties or anonymous objects to join, and use into and DefaultIfEmpty() to simulate a LEFT JOIN.
3. GroupBy: Groups results across multiple columns using an anonymous type. Use Distinct() to count aggregated results.
4. Select: Select the required columns by creating an anonymous type with the specified fields.

The following is LINQ code written in query expression syntax:

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 following is the equivalent LINQ code written in Lambda expression syntax:

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(subr2, 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 }) //处理r可能为null的情况
                  .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 
                  }).Distinct();

Pay attention to the processing of cvr.r.cnt in the Lambda expression version. The ?. null conditional operator is added to handle the situation where r may be null, avoiding NullReferenceException exceptions. Both methods accomplish the same functionality, and which method you choose comes down to personal preference.

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