Linq to SQL: Simple group by generating many SQL q

Question

Here's the C#/Linq:

Fbc_tickets.GroupBy(t => t.Fbt_household_id)

LinqPad tells me this is being translated into (MySQL):

SELECT t0.fbt_household_id
FROM fbc_ticket AS t0
GROUP BY t0.fbt_household_id

SELECT t0.fbc_ticket_id, t0.fbt_client_id, ...
FROM fbc_ticket AS t0
WHERE ((t0.fbt_household_id IS NULL AND @n0 IS NULL) OR (t0.fbt_household_id = @n0))
-- n0 = [1]

SELECT t0.fbc_ticket_id, t0.fbt_client_id, ...
FROM fbc_ticket AS t0
WHERE ((t0.fbt_household_id IS NULL AND @n0 IS NULL) OR (t0.fbt_household_id = @n0))
-- n0 = [2]

...

Why is it generating all these queries? I'd expect something more akin to

SELECT * FROM fbc_ticket GROUP BY fbt_household_id

And that's it...

fbt_household_id is an unsigned int and non-nullable.

Answer 1

I see this all the time in linq-to-sql. First a query to obtain the grouping keys, followed by a query per key to populate the groups. I don't know why it is implemented this way, but there is not much you can do about it, I'm afraid. Entity Framework is a lot smarter in this particular area (not in others).

Linqpad also does it when I connect to an existing linq-to-sql context (and don't let it create a context on the fly), so I don't think it's got anything to do with Linqpad.