Linq Group By Count single column

本文关键字：single column Count By Group Linq | 更新日期: 2023-09-27 18:08:41

我的linq如下:

from ucd in UserCategoryDetails
join uc in UserCategories on ucd.UserCategoryDetailsID equals
        uc.UserCategoryDetailsID into ucj from uc in ucj.DefaultIfEmpty()
join uct in UserCategoryTypes on ucd.UserCategoryTypeID equals
        uct.UserCategoryTypeID
join cc in UserCategoryColours on ucd.UserCategoryColourID equals 
         cc.UserCategoryColourID
where 
        ucd.UserCategoryTypeID == 2 && 
        ucd.UserID == 1
group ucd by new { 
        ucd.UserCategoryDetailsID, 
        ucd.CategoryName, 
        cc.UserCategoryColourID, 
        cc.ImageSrcLarge, 
        cc.ImageSrcSmall 
    } into g
select new  {
        UserCategoryDetailsID = g.Key.UserCategoryDetailsID,
        CategoryName = g.Key.CategoryName,
        CategoryColourID = g.Key.UserCategoryColourID,
        ImageSrcLarge = g.Key.ImageSrcLarge,
        ImageSrcSmall = g.Key.ImageSrcSmall,
        CategoryCount = g.Count()
}

问题是Count()生成的sql是COUNT(*)，这混淆了结果，因为当UserCategories中没有行时它返回1。

谁能告诉我如何生成的LINQ等效COUNT(uc.ProjectID)请?基本上是下面的SQL语句，但在linq:

SELECT 
    [t0].[UserCategoryDetailsID], [t0].[CategoryName], 
    [t3].[UserCategoryColourID], [t3].[ImageSrcLarge], 
    [t3].[ImageSrcSmall], COUNT([t1].ProjectID) AS [CategoryCount]
FROM 
    [UserCategoryDetails] AS [t0]
        LEFT OUTER JOIN [UserCategory] AS [t1] ON 
            [t0].[UserCategoryDetailsID]) = [t1].[UserCategoryDetailsID]
        INNER JOIN [UserCategoryType] AS [t2] ON 
            [t0].[UserCategoryTypeID] = [t2].[UserCategoryTypeID]
        INNER JOIN [UserCategoryColour] AS [t3] ON 
            [t0].[UserCategoryColourID] = [t3].[UserCategoryColourID]
WHERE 
    ([t0].[UserCategoryTypeID] = 2) AND ([t0].[UserID] = 1)
GROUP BY 
    [t0].[UserCategoryDetailsID], [t0].[CategoryName], 
    [t3].[UserCategoryColourID], [t3].[ImageSrcLarge], 
    [t3].[ImageSrcSmall]

Linq Group By Count single column

我可能是错的，当然，但在我看来，你的LINQ查询的COUNT(*)是对分组操作，而不是对UserCategory。

用uc.Count()代替g.Count()会发生什么?

如果表没有行，

COUNT(*)不会返回1。只有当表中有1行或多行，并且ProjectId列在其中一行或多行中为NULL时，COUNT(ProjectId)才会低于COUNT(*)。

我明白了。看起来，因为它正在做COUNT(*)，当项目id为null时，它将返回null，如Jay所提到的。这将在计数中被视为1，从而破坏结果。

将select的count部分更改为下面的效果很好:

CategoryCount = g.Where(grp => grp != null).Count()

感谢您的回复

相关文章：