LINQ Count returning 1 instead of zero for an empty group

Question

I've got this SQL query:

SELECT oy.ownerId, oy.Year,  COUNT(doc.Id) as docCount FROM aavabruf.owneryears oy 
left join vastdocuments doc
on oy.ownerId = doc.Ownerid and  oy.Year =  doc.Year
group by oy.ownerid, oy.year
order by docCount

It shows docCount as ZERO for the OwnerId, Year pairs that have no document match in the vastdocuments table.

I tried to do the same with LINQ using the suggested left outer join solution:

from oy in OwnerYears
join doc in VaStDocuments on new {oy.OwnerId, oy.Year} equals new {doc.OwnerId , doc.Year} into docS
from docIfNull in docS.DefaultIfEmpty()
group oy by new {oy.OwnerId, oy.Year} into g
orderby g.Count() ascending
select new { OwnerId = g.Key.OwnerId,  Year = g.Key.Year, docCount = g.Count()}

However, for the OwnerId, Year groups that are not present in the VastDocuments table I get docCount as ONE, not ZERO. If I remove the

from docIfNull in docS.DefaultIfEmpty()

line the "empty" groups will not be shown at all.

How can i get the Count as zero just as it is in the SQL query? I tried the following:

Count = docIfNull == null ? 0 : g.Count()

however in this case I get an error:

The name 'docIfNull' does not exist in the current context

Trace the query and check it within the database itself as tsql? — Masoud Andalibi, Jun 19 '17 at 16:24

score 5 · Answer 1 · answered Jun 19 '17 at 16:35

The simplest approach is to count non-null values:

g.Count(x => x != null)

I'd suggest moving the ordering after the select so that you can avoid repeating yourself:

select new { g.Key.OwnerId, g.Key.Year, DocCount = g.Count(x => x != null) } into result
orderby result.DocCount
select result

However, I note that currently you're not using docIfNull at all at the moment... so I suspect your join isn't really doing what you want it to. Perhaps you should be using

group docIfNull by new { oy.OwnerId, oy.Year } into g

?

Ivan Stoev · Accepted Answer · 2017-06-19T16:59:41.443

SQL COUNT function ignores the NULL values, while LINQ Count function w/o predicate counts everything, including nulls.

You can get the same result in LINQ by using the predicate version of Count like this (note the group docIfNull so the g elements will be of the same type as docIfNull):

from oy in OwnerYears
join doc in VaStDocuments on new { oy.OwnerId, oy.Year } equals new { doc.OwnerId, doc.Year } into docS
from docIfNull in docS.DefaultIfEmpty()
group docIfNull by new { oy.OwnerId, oy.Year } into g
let docCount = g.Count(doc => doc != null)
orderby docCount ascending
select new { OwnerId = g.Key.OwnerId, Year = g.Key.Year, docCount = docCount }

(the let clause is just to reuse the expression in orderby and select).

However in LINQ you have another option - in case the (OwnerId, Year) combination inside OwnerYears is unique as it seems, instead of left outer join pattern followed by group by and Count filtering nulls you could use simple group join operator with regular Count call:

from oy in OwnerYears
join doc in VaStDocuments on new { oy.OwnerId, oy.Year } equals new { doc.OwnerId, doc.Year } into docs
let docCount = docs.Count()
orderby docCount ascending
select new { OwnerId = oy.OwnerId,  Year = oy.Year, docCount = docCount }

LINQ Count returning 1 instead of zero for an empty group

2 Answers2

Linked