Linq Group By Count single column

I have my linq as below:

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 equa开发者_StackOverflowls
        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()
}

The trouble is the sql the Count() generates is COUNT(*), which is messing up the results as it returns 1 when the are no rows in UserCategories.

Can someone show me how to generate the LINQ equivalent of COUNT(uc.ProjectID) please? Basically the following SQL statement but in 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]

---

I could be mistaken, of course, but it looks to me like your LINQ query's COUNT(*) is operating on the grouping, rather than on UserCategory.

What happens if you replace g.Count() with uc.Count()?

COUNT(*) won't return 1 if the table has no rows. COUNT(ProjectId) will be lower than COUNT(*) only if there are 1 or more rows in the table and the ProjectId column is NULL in one or more of those rows.

---

I figured it out. It would appear that because it was doing COUNT(*), it would return null when the project id was null, as mentioned by Jay. This would then be seen as 1 in the count, screwing up the results.

Changing the count part of the select to below works nicely:

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

Thanks for your replies