.net – Linq with Left Join on SubQuery contains Count

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

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

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

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!