我在将sql转换为
linq语法时遇到困难.
我有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 };
