c# - LINQ 查询性能低下

Question

我有这个 t-sql 查询来自在 Cold Fusion 上运行的旧系统。此查询返回记录所需的时间不到一秒钟。

select  dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'}) p, count(*) c 
from    account 
where   createdAt <= {ts '2015-02-28 23:59:59'} 
and accountType = 'business' 
and dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'}) <12 
group by    dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'}) 
order by    dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'})

我现在正在使用 .NET 和 LINQ 将其转换为新系统。我设法编写了这个 LINQ 查询，它给出了相同的结果。

from a in db.Accounts
where SqlFunctions.DateDiff("Month", SqlFunctions.DateAdd("Hour", 11, a.createdAt), "2015-02-28 23:59:59") < 12
&& a.accountType == "business"
group a by SqlFunctions.DateDiff("Month", a.createdAt, "2015-02-28 23:59:59") into grp
orderby SqlFunctions.DateDiff("Month", grp.FirstOrDefault().createdAt, "2015-02-28 23:59:59")
select new ProgressViewModel.Data
{
     date = SqlFunctions.DateDiff("Month", grp.FirstOrDefault().createdAt, "2015-02-28 23:59:59"),
     amount = grp.Count()
});

但是，此查询的运行时间不少于 5 秒，而第一个查询 (t-sql) 的运行时间不到 1 秒。

通过使用 Glimpse，我们可以看到该 LINQ 查询生成的 t-sql。它有多个子选择，比快速查询长 5 倍。

如何改进 LINQ 查询？

Answer 1

尝试这样的事情在分组之前将其带入内存:

from ca in (
    from a in db.Accounts
    where SqlFunctions.DateDiff("Month", SqlFunctions.DateAdd("Hour", 11, a.createdAt), "2015-02-28 23:59:59") < 12 && a.accountType == "business"
    select a.createdAt).ToArray()
group a by new /* month diff */ into grp
orderby grp.Key
select new ProgressViewModel.Data
{
    date = grp.key,
    amount = grp.Count()
});