LINQ Count returning 1 instead of zero for an empty group

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

Jon Skeet
people
quotationmark

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

?

people

See more on this question at Stackoverflow