LINQ与左连接上包含的子查询计数

LINQ与左连接上包含的子查询计数

本文介绍了LINQ与左连接上包含的子查询计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有困难的转换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 };

这篇关于LINQ与左连接上包含的子查询计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-31 03:32