c# - LinqToSQL-ToList()看起来非常慢

我是LinqToSQL的新手，我正在从事的项目无法更改为其他项目。我正在将一些旧的SQL代码转换为Linq。我对linq不那么热衷，所以我用Linqer为我做翻译。该查询花了大约90秒钟来运行，所以我认为它必须是linqToSQL。但是，当我复制LinqToSQL生成的查询并在datacontext上运行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的约束，但大约有19,000条记录。好像要全部获取19,000，然后在得到记录后检查 0。我也将Linq更改为投影到上面使用的类中，而不是匿名类型，但这没什么区别这是原始的SQL：SELECT InvoiceNum, Max(AccountCode), Sum(AmountInc) AS BalanceFROM (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 tGROUP BY InvoiceNumHAVING (Sum(t.AmountInc)<>0)ORDER BY InvoiceNum这就是linqvar 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 (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 我敢打赌，问题出在这一行：where Convert.ToDecimal(g.Sum(p => p.Balance)) != 0m可能正在发生的事情是，它无法将其转换为SQL，并且无声地尝试将所有行从db获取到内存，然后对内存对象（LINQ到对象）进行过滤也许尝试将其更改为：where g.Sum(p=>.Balance!=0) (adsbygoogle = window.adsbygoogle || []).push({});