0

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.

mpen
  • 272,448
  • 266
  • 850
  • 1,236
  • What are you doing with your Linq result? Looping through it? – Joachim Isaksson Sep 28 '12 at 19:01
  • Do not trust LinqPad, run the query and use SQL profiler to see what is actually submitted. The results you are getting require more then `Fbc_tickets.GroupBy(t => t.Fbt_household_id)` to be submitted. Are you using the query (or one like it) in other places? – Trisped Sep 28 '12 at 19:04
  • @JoachimIsaksson: No. I'm not doing anything with it. That's the whole thing. I just want the SQL back out. – mpen Sep 28 '12 at 19:23
  • @Trisped: Hrm? SQL profiler? I've got some hand-written SQL in other places, but I'm trying to write a rather complicated query and I was hoping Linq would help me out. That, and I just wanted to play with it and learn it better. – mpen Sep 28 '12 at 19:25
  • @Mark The reason I'm asking is that Linq expressions aren't usually even evaluated unless you even do ToList(), FirstOrDefault() or similar on the result. – Joachim Isaksson Sep 28 '12 at 19:40
  • @JoachimIsaksson: I don't know what LinqPad does under the hood, but I'm sure it evaluates the result set once so that it can display it. Whether it just does a simple `foreach` or a `ToList()` first, I'm not sure. – mpen Sep 28 '12 at 19:43
  • Ah, did not notice MySQL. You could use http://stackoverflow.com/questions/20263/is-there-a-profiler-equivalent-for-mysql to see what query the LINQ code is generating. In my experience LinqPad is not always correct. You could also check the connection log. In short, LinqPad is probably running your query 3 times, creating your issue (once with `Fbc_tickets.GroupBy(t => t.Fbt_household_id)`, once with `Fbc_tickets.Where(t => t.Fbt_household_id = 1).GroupBy(t => t.Fbt_household_id)`, and once with `Fbc_tickets.Where(t => t.Fbt_household_id = 2).GroupBy(t => t.Fbt_household_id)`). – Trisped Sep 28 '12 at 20:41
  • Oh I see, I missed the part where you were getting every column from the grouped table (not just the ones which were in the group by or in agregates. – Trisped Sep 29 '12 at 00:57

1 Answers1

2

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.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • Yes, I'd imagine it's the SQL driver "IQ" that's doing it. What's the difference between EF and Linq-to-SQL? As I understand it LinqPad is creating C# classes with properties under the hood (or rather a CLR assembly), much like an EF model, and then beyond that, wouldn't they use the exact same drivers to translate the expression tree (Linq) into SQL? – mpen Sep 29 '12 at 00:00
  • 1
    Yes, of course Linqpad has no query engine. It builds an `AppDomain` with a linq-to-sql context if one connects to a database. EF hardly ever (maybe never) emits more that one query per linq statement. The queries can be monstrous at times, but at least it is one shot. To use EF in Linqpad you can connect to an assembly that contains the EF context. – Gert Arnold Sep 29 '12 at 09:44