Linq with Left Join on SubQuery contains Count [英] Linq with Left Join on SubQuery containing Count

问题描述

我无法将sql翻译成linq语法。

I'm having difficulty translating sql to linq syntax.

我有两个使用CategoryID引用的表（Category和CategoryListing）。我需要获得类别表中所有类别ID的列表，以及类别列表中所有相应匹配的类别ID计数。如果类别列表中没有CategoryID，那么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 with Left Join on SubQuery contains Count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！