如何有效地將復雜的SQL查詢轉換為多個加入併計數為LINQ？

將包含多個連接、計數和左連接的SQL轉換為LINQ

SQL 查詢

考慮以下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

LINQ 查詢表達式

要將此SQL查詢轉換為LINQ查詢表達式，請遵循以下準則：

1. 將子查詢轉換為單獨聲明的變量。
2. 按照LINQ子句順序轉換每個子句。
3. 將JOIN子句轉換為導航屬性或匿名對象。
4. 使用GroupJoin和DefaultIfEmpty()模擬LEFT JOIN。

應用這些規則會產生以下LINQ查詢表達式：

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

Lambda表達式

對於lambda表達式轉換，進一步將LEFT JOIN轉換為GroupJoin...SelectMany：

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

以上是如何有效地將復雜的SQL查詢轉換為多個加入併計數為LINQ？的詳細內容。更多資訊請關注PHP中文網其他相關文章！