LinqToSQL-ToList()似乎非常慢

我是LinqToSQL的新手，我正在处理的项目不能更改为其他项目。我正在将一些旧的SQL代码翻译成Linq。由于对linq不太感兴趣，我使用了Linqer为我做翻译。查询运行大约需要90秒，所以我认为它一定是linqToSQL。然而，当我复制LinqToSQL生成的查询并在数据上下文上运行ExecuteQuery时，它的速度非常快。我已经复制了完整的查询，而不是试图将其提炼出来，但问题似乎与LinqToSQL在幕后所做的事情有关。

总之，如果我复制linq创建的T-SQL并运行

var results = DB.ExecuteQuery<InvoiceBalanceCheckDTO.InvoiceBalanceCheck>(@"T-SQL created by Linq - see below").ToList()

它在大约0.5秒内以预期的结果完成。它大约在同一时间直接在SSMS中运行。但是，如果我使用创建T-SQL的linqToSQL代码并执行ToList（），则需要很长时间。结果只有9条记录，尽管没有检查余额的约束<>0，将有大约19000条记录。就好像它得到了全部19000，然后检查<>0在它得到记录之后。我还将Linq改为投影到上面使用的类中，而不是匿名类型，但这对没有影响

这是原始SQL:

SELECT InvoiceNum, Max(AccountCode), Sum(AmountInc) AS Balance
FROM 
    (SELECT InvoiceNum, AccountCode, AmountInc From TourBookAccount WHERE AccDetailTypeE IN(20,30) AND InvoiceNum >= 1000 
    UNION ALL 
    SELECT InvoiceNum, '<no matching invoice>' AS AccountCode, AccountInvoiceDetail.AmountInc 
    FROM AccountInvoiceDetail 
        INNER JOIN AccountInvoice ON AccountInvoiceDetail.InvoiceID=AccountInvoice.InvoiceID 
    WHERE AccDetailTypeE IN(20,30) 
    AND InvoiceNum >= 1000 
    ) as t
GROUP BY InvoiceNum 
HAVING (Sum(t.AmountInc)<>0) 
ORDER BY InvoiceNum

这是linq

var test =  (from t in
                        (
                            //this gets the TourBookAccount totals
                            from tba in DB.TourBookAccount
                            where
                            detailTypes.Contains(tba.AccDetailTypeE) &&
                            tba.InvoiceNum >= dto.CheckInvoiceNumFrom
                            select new 
                            {
                                InvoiceNum = tba.InvoiceNum,
                                AccountCode = tba.AccountCode,
                                Balance = tba.AmountInc
                            }
                        )
                        .Concat //note that concat, since it's possible that the AccountInvoice record does not actually exist
                        (
                            //this gets the Invoice detail totals.
                            from aid in DB.AccountInvoiceDetail
                            where
                            detailTypes.Contains(aid.AccDetailTypeE) &&
                            aid.AccountInvoice.InvoiceNum >= dto.CheckInvoiceNumFrom &&
                            select new 
                            {
                                InvoiceNum = aid.AccountInvoice.InvoiceNum,
                                AccountCode = "<No Account Records>",
                                Balance = aid.AmountInc
                            }
                        ) 
                group t by t.InvoiceNum into g
                where Convert.ToDecimal(g.Sum(p => p.Balance)) != 0m
                select new 
                {
                    InvoiceNum = g.Key,
                    AccountCode = g.Max(p => p.AccountCode),
                    Balance = g.Sum(p => p.Balance)
                }).ToList();

这就是linq产生的T-SQL

  SELECT [t5].[InvoiceNum], [t5].[value2] AS [AccountCode], [t5].[value3] AS [Balance]
FROM (
    SELECT SUM([t4].[AmountInc]) AS [value], MAX([t4].[AccountCode]) AS [value2], SUM([t4].[AmountInc]) AS [value3], [t4].[InvoiceNum]
    FROM (
        SELECT [t3].[InvoiceNum], [t3].[AccountCode], [t3].[AmountInc]
        FROM (
            SELECT [t0].[InvoiceNum], [t0].[AccountCode], [t0].[AmountInc]
            FROM [dbo].[TourBookAccount] AS [t0]
            WHERE ([t0].[AccDetailTypeE] IN (20, 30)) AND ([t0].[InvoiceNum] >= 1000)
            UNION ALL
            SELECT [t2].[InvoiceNum],'<No Account Records>' AS [value], [t1].[AmountInc]
            FROM [dbo].[AccountInvoiceDetail] AS [t1]
            INNER JOIN [dbo].[AccountInvoice] AS [t2] ON [t2].[InvoiceID] = [t1].[InvoiceID]
            WHERE ([t1].[AccDetailTypeE] IN (20, 30)) AND ([t2].[InvoiceNum] >= 1000)
            ) AS [t3]
        ) AS [t4]
    GROUP BY [t4].[InvoiceNum]
    ) AS [t5]
WHERE [t5].[value] <> 0