c# - EF 5.0 中长时间运行的查询

Question

以下查询应返回大约 7200 条记录:

            using (var context = new RapEntities())
        {
            context.Configuration.ProxyCreationEnabled = false;

            var query = from i in context.QbTxnItems.AsNoTracking()
                        where (i.ListType == "Invoice")
                        && !context.Payments.Any(p => p.QbTxnId == i.QbTxnId && p.QbTxnId != null)
                        && !context.QbTxnIgnores.Any(ti => ti.QbTxnId == i.QbTxnId)
                        orderby i.RefNumber
                        select i;

            var items = RapEntities.GetList(query);

生成的 sql(来自 sql server profiler:

SELECT 
[Extent1].[QbTxnItemId] AS [QbTxnItemId], 
[Extent1].[ListType] AS [ListType], 
[Extent1].[QbTxnId] AS [QbTxnId], 
[Extent1].[QbEditSequence] AS [QbEditSequence], 
[Extent1].[TxnNumber] AS [TxnNumber], 
[Extent1].[RefNumber] AS [RefNumber], 
[Extent1].[TxnDate] AS [TxnDate], 
[Extent1].[TxnAmt] AS [TxnAmt], 
[Extent1].[IsPaid] AS [IsPaid], 
[Extent1].[IsCleared] AS [IsCleared], 
[Extent1].[LastGetAll] AS [LastGetAll], 
[Extent1].[GetIsCleared] AS [GetIsCleared], 
[Extent1].[LastModified] AS [LastModified], 
[Extent1].[Version] AS [Version], 
[Extent1].[RecordStatus] AS [RecordStatus], 
[Extent1].[UserId] AS [UserId], 
[Extent1].[TableID] AS [TableID]
FROM [dbo].[QbTxnItems] AS [Extent1]
WHERE (N'Invoice' = [Extent1].[ListType]) AND ( NOT EXISTS (SELECT 
1 AS [C1]
FROM [dbo].[Payments] AS [Extent2]
WHERE ([Extent2].[QbTxnId] = [Extent1].[QbTxnId]) AND ([Extent2].[QbTxnId] IS NOT NULL)
)) AND ( NOT EXISTS (SELECT 
1 AS [C1]
FROM [dbo].[QbTxnIgnores] AS [Extent3]
WHERE [Extent3].[QbTxnId] = [Extent1].[QbTxnId]
))
ORDER BY [Extent1].[RefNumber] ASC

从 Entity Framework 执行时不会在任何合理的时间内完成，但会从 SSMS 立即执行。

使用 take(200) 将行数限制为 200，即使从 EF 调用，查询也会在大约 50 毫秒内运行。将行数增加到 500 会使时间增加到超过 5 秒。

这似乎是不恰当的表现。 EF 必须能够在合理的时间内返回超过几百行。是否可以调整任何设置以提高从 EF 运行更大查询的能力？

Answer 1

在我看来，您可以通过在此处实际执行一些连接来加快速度。试试这个:

    var query = (from i in context.QbTxnItems.AsNoTracking()
                join p in context.Payments on i.QbTxnId equals p.QbTxnId
                join qi in context.QbTxnIgnores on i.QbTxnId equals qi.QbTxnId
                where (i.ListType == "Invoice")
                select i).OrderBy(i => i.RefNumber);