在包含计数的子查询上使用左连接的 Linq [英] Linq with Left Join on SubQuery containing Count

问题描述

我在将 sql 转换为 linq 语法时遇到了困难.

I'm having difficulty translating sql to linq syntax.

我有 2 个表(Category 和 CategoryListing)，它们使用 CategoryID 相互引用.我需要获取 Category 表中所有 CategoryID 的列表以及 CategoryListing 表中所有相应匹配项的 CategoryID 计数.如果 CategoryID 不存在于 CategoryListing 中，则仍应返回 CategoryID - 但频率为 0.

I have 2 tables (Category and CategoryListing) which reference each other with CategoryID. I need to get a list of all the CategoryID in Category Table and the Count of CategoryID for all corresponding matches in the CategoryListing table. If a CategoryID is not present in CategoryListing, then the CategoryID should still be returned - but with a frequency of 0.

以下 sql 查询演示了预期的结果:

The following sql query demonstrates expected results:

SELECT c.CategoryID, COALESCE(cl.frequency, 0) as frequency
FROM Category c
LEFT JOIN (
    SELECT cl.CategoryID, COUNT(cl.CategoryID) as frequency 
    FROM CategoryListing cl
    GROUP BY cl.CategoryID
) as cl
ON c.CategoryID = cl.CategoryID
WHERE c.GuideID = 1

推荐答案

未测试，但这应该可以解决问题:

Not tested, but this should do the trick:

var q = from c in ctx.Category
        join clg in 
            (
                from cl in ctx.CategoryListing
                group cl by cl.CategoryID into g
                select new { CategoryID = g.Key, Frequency = g.Count()}
            ) on c.CategoryID equals clg.CategoryID into cclg
        from v in cclg.DefaultIfEmpty()
        where c.GuideID==1
        select new { c.CategoryID, Frequency = v.Frequency ?? 0 };

这篇关于在包含计数的子查询上使用左连接的 Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！