我有困难的转换SQL到LINQ语法。

我有2表（类别和CategoryListing）的相互引用与类别ID。我需要让所有的类别ID在类别表和类别ID的在CategoryListing表中的所有相应匹配的计数的列表。如果一个类别id并不present在CategoryListing，那么类别id仍应返回 - 但频率0

下面的SQL查询演示了预期的结果：

  SELECT c.CategoryID，COALESCE（cl.frequency，0）频率
从C类
LEFT JOIN（
    SELECT cl.CategoryID，COUNT（cl.CategoryID）频率
    从CategoryListing CL
    GROUP BY cl.CategoryID
）作为CL
ON c.CategoryID = cl.CategoryID
WHERE c.GuideID = 1
 

解决方案

没测试过，但是这应该做的伎俩：

 变种Q =从C在ctx.Category
    加入CLG在
    （
    从CL在ctx.CategoryListing
    CL集团通过cl.CategoryID为G
    选择新{类别id = g.Key，频率= g.Count（）}
    ）在c.CategoryID等于clg.CategoryID到cclg
    从V IN cclg.DefaultIfEmpty（）
    其中，c.GuideID == 1
    选择新{c.CategoryID，频率=垂直频率？ 0};
 

I'm having difficulty translating sql to linq syntax.

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.

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