Linq to SQL:通过生成许多SQL查询进行简单分组 [英] Linq to SQL: Simple group by generating many SQL queries

问题描述

这是C#/Linq:

Fbc_tickets.GroupBy(t => t.Fbt_household_id)

LinqPad告诉我这正在被翻译成(MySQL):

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

就是这样...

fbt_household_id是无符号int且不可为空.

fbt_household_id is an unsigned int and non-nullable.

推荐答案

我一直在linq-to-sql中看到这一点.首先是查询以获取分组键，然后是每个键的查询以填充组.我不知道为什么以这种方式实现它，但是恐怕您对此无能为力.在这个特定领域(不是其他领域)，实体框架要聪明得多.

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

当我连接到现有的linq-to-sql上下文(并且不要让它动态创建上下文)时，Linqpad也会执行此操作，因此我认为与Linqpad无关.

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.

这篇关于Linq to SQL:通过生成许多SQL查询进行简单分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！