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

Question

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().

How could the count be 0? Any row with a count of zero wouldn't be in the results at all. Maybe I've missed what the SQL is trying to accomplish. (If you could do the same with a simpler example which didn't have 5 different joins, it would be easier to help you.) — Jon Skeet, Oct 15 '15 at 19:05
@JonSkeet That's why I left join with AVLR. Otherwise you would be right, it would only shows the ones that contains in AVLR — GustavoAndrade, Oct 15 '15 at 19:11
@VojtěchDohnal I tried that in the link you posted. It doesn't work for me. Maybe I'm doing the LINQ query wrong. I'm trying to make it look like the SQL query, maybe that's the wrong approach — GustavoAndrade, Oct 15 '15 at 19:14
You're grouping after the left join though... What does a group with 0 elements in look like? — Jon Skeet, Oct 15 '15 at 19:16
@JonSkeet it wil contain everything besides the columns from "r". That's why I do COUNT(r.ID), but can be any column of "r" — GustavoAndrade, Oct 15 '15 at 19:23
No, it won't. Basically, group by never results in an empty group. It takes elements with the same key, and groups them together. If there's no element with a particular key, there's no group for that key. — Jon Skeet, Oct 15 '15 at 19:23
@JonSkeet Yeah, I get your point. That's why it will show count as 0, because it doesn't return any group. SQL Server will still generate a result for that though, showing the count as 0. — GustavoAndrade, Oct 15 '15 at 19:34
This is why it would be easier to help you if you showed a simpler example, with sample input and expected output. I know what LINQ will do, but I don't fully understand what you're trying to achieve. — Jon Skeet, Oct 15 '15 at 19:35
I think when the OP says the count is 0 he is talking about the original SQL query that he is trying to duplicate. If you perform a left join and there is no match in the right table then `r.Id` (value in the right table) will be null and `COUNT(r.Id)` will be 0 (at least that is my experience with t-sql). That is the behavior he is trying to recreate with linq, I believe. I find left joins in linq to be very awkward. — Jason Boyd, Oct 15 '15 at 19:37
@JasonBoyd yes, that's exactly what I want to do. Thanks for explaining it better than me. — GustavoAndrade, Oct 15 '15 at 19:46

Jon Skeet · Answer 1 · 2015-10-15T20:00:28.830

3

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) };

edited Oct 15 '15 at 20:00

answered Oct 15 '15 at 19:53

Jon Skeet

1,421,763
867
9,128
9,194

Is `qs` required in `new { qs, rr }`? not used anywhere, and `rr` is only there for the count... all other selected values come from the key... Isn't `group rr by new...` enough (and then `g.Count(x => x != null)`)? – Amit Oct 15 '15 at 19:55
@Amit: Hmm... you could be right. Will add that as a second option. – Jon Skeet Oct 15 '15 at 19:59
Actually it was right in front of us the whole time. Gustavo said that `Count()` get's QS in his code. All it took was to replace `qs` with `rr`. The real problem (as you stated in the comments) was missing MCVE. – Amit Oct 15 '15 at 20:04
@Amit: Yup - my problem is that I'm pretty dodgy on SQL, even though I know LINQ pretty well. That's where a short but complete example with sample input and output is incredibly helpful :) Someone smarter on the SQL could probably have understood it more easily... – Jon Skeet Oct 15 '15 at 20:06
Yeah, that was my mistake. I grouped qs instead of rr. Thanks very much! – GustavoAndrade Oct 15 '15 at 20:08
For me it's the opposite :-) I got the SQL part straight away, then it took me some time sifting through MSDN to connected the dots of `group .. by ..`. Got back to write an answer and immediately saw yours :-) – Amit Oct 15 '15 at 20:09
I understood the SQL just fine, but I'm weak on query syntax LINQ. – Robert McKee Oct 15 '15 at 20:09
Interesting, although this produces very different (longer) t-sql query using DISTINCT instead of GROUP BY, the execution plans are the same on SQL Server. – Vojtěch Dohnal Oct 15 '15 at 20:24

Robert McKee · Accepted Answer · 2015-10-15T20:01:46.127

2

Just taking a stab:

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
orderby 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!=null) };

edited Oct 15 '15 at 20:01

answered Oct 15 '15 at 19:14

Robert McKee

21,305
1
43
57

That doesn't work because "x" is "qs" and "qs" doesn't know "r_1" or anything about "r" – GustavoAndrade Oct 15 '15 at 19:20
What's actually in the group then? qs is the key. – Robert McKee Oct 15 '15 at 19:23
Throw this into Linqpad: `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 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 }` and post a screenshot of the result, and can get you an answer pretty quick. – Robert McKee Oct 15 '15 at 19:25
@RobertMcKee the Key is that anonymous object I'm generating. But strangely the predicate it expects is based on "qs" – GustavoAndrade Oct 15 '15 at 19:37
@RobertMcKee What you did was just return g in Qty. That will make it resturn an object with the Key and a list of IGROUPING<,AVLQS> with the quantity that would show in .Count().... – GustavoAndrade Oct 15 '15 at 19:43
Updated answer. Yes, that's what I wanted to know. What was in the actual grouping. You grouped the wrong thing. Fixed that. Sorry, I don't use the query syntax very often. – Robert McKee Oct 15 '15 at 20:03
I'm accepting your answer because you did it first, but both answers are correct. What I was doing wrong was grouping the wrong table – GustavoAndrade Oct 15 '15 at 20:11
Made my day. Getting an answer before Jon. I thought it was impossible, but apparently it's just ultra rare. – Robert McKee Oct 15 '15 at 20:19
Looking closely at editing times, Jon was still a bit faster than your last edit. – Vojtěch Dohnal Oct 15 '15 at 20:29
True, that was just updating the linqpad image with the new code though. – Robert McKee Oct 15 '15 at 20:32

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

2 Answers2