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
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
?
See more on this question at Stackoverflow