Linq with group by returning count 1 when when it should be 0

I'm having a bit of a trouble with a linq query which counts the group by returns. For results that returns 0 in the MSSQL query, it's returning 1. Here is the LINQ:

from qs in Ctx.QS
join qq in Ctx.QQ on qs.ID equals qq.QSID
join qt in Ctx.QT on qq.QTID equals qt.ID
join qv in Ctx.QV on qt.QID equals qv.QID
join qi in Ctx.QI on qs.ID equals qi.QSID
join r in Ctx.R on qi.ID equals r.QIID into r_1
from rr in r_1.DefaultIfEmpty()
where qv.ID == 1
group qs by new { qsId = qs.ID, qtId = qt.ID, qt.Title, qs.Name, qi.Group } into g
orderby g.Key.qsId
orderby g.Key.qtId
select new { g.Key.qsId, g.Key.qtId, g.Key.Title, g.Key.Name, g.Key.Group, Qty = g.Count() };

I'm trying to translate this SQL query:

SELECT qs.ID, qt.ID, qt.TITLE, qs.NAME, qi.GROUP, COUNT(r.ID)
FROM AVLQS qs
INNER JOIN AVLQQ qq ON qs.ID = qq.QSID
INNER JOIN AVLQT qt ON qq.AVLQTID = qt.ID
INNER JOIN AVLQV qv ON qt.AVLQID = qv.AVLQID
INNER JOIN AVLQI qi ON qs.ID = qi.AVLQSID
LEFT JOIN AVLR r ON qi.ID = r.AVLQIID
WHERE qv.ID = 1
GROUP BY qs.ID, qt.ID, qt.TITLE, qs.NAME, qi.GROUP
ORDER BY qt.ID, qs.ID

The translation is almost 100%, the only difference I see is in the Count() function, but I can't find a way to do in LINQ what SQL does in COUNT(r.ID) because in LINQ the predicate that accepts inside Count() function expects "QS" and not (R).

My case is a bit more complex than the one in the "duplicate" question because the object that is expected inside Count() in my case, doesn't know about the right sub-object that it should check inside Count().

Jon Skeet
people
quotationmark

It sounds like you need to make two changes to fix this part - and another to fix ordering.

If you want to find the count of the elements where the left join actually matched something (rather than "using" the DefaultIfEmpty call) you probably want to include rr in your grouping elements - and then check whether or not it's null within the Count call.

You also want to change your orderby clause, because having two orderby clauses doesn't mean wha\t you want it to :) I suspect you want:

from qs in Ctx.QS
join qq in Ctx.QQ on qs.ID equals qq.QuestaoID
join qt in Ctx.QT on qq.QTID equals qt.ID
join qv in Ctx.QV on qt.QID equals qv.QID
join qi in Ctx.QI on qs.ID equals qi.QSID
join r in Ctx.R on qi.ID equals r.QIID into r_1
from rr in r_1.DefaultIfEmpty()
where qv.ID == 1
group new { qs, rr } by new { qsId = qs.ID, qtId = qt.ID, qt.Title, qs.Name, qi.Group } into g
orderby g.Key.qsId, g.Key.qtId
select new { g.Key.qsId, g.Key.qtId, g.Key.Title, g.Key.Name, g.Key.Group, Qty = g.Count(x => x.rr != null) };

Or, as Amit pointed out, you could just include rr in the group:

from qs in Ctx.QS
join qq in Ctx.QQ on qs.ID equals qq.QuestaoID
join qt in Ctx.QT on qq.QTID equals qt.ID
join qv in Ctx.QV on qt.QID equals qv.QID
join qi in Ctx.QI on qs.ID equals qi.QSID
join r in Ctx.R on qi.ID equals r.QIID into r_1
from rr in r_1.DefaultIfEmpty()
where qv.ID == 1
group rr by new { qsId = qs.ID, qtId = qt.ID, qt.Title, qs.Name, qi.Group } into g
orderby g.Key.qsId, g.Key.qtId
select new { g.Key.qsId, g.Key.qtId, g.Key.Title, g.Key.Name, g.Key.Group, Qty = g.Count(rr => rr != null) };

people

See more on this question at Stackoverflow