c# - 由 C# EF 核心调用时的暂停选择查询，MSSQL 管理工作室可以很好地执行查询。为什么？

Question

我无法为您提供足够的数据来重现错误，因此我将尽可能多地提供数据。

我有一个从 EF 核心执行的选择语句。

var bookings = context.Booking
                      .Where(booking => booking.ConsigneeNumber == customer.GetCustomerTarget().Code 
                             && booking.CreatedAt >= from 
                             && booking.CreatedAt < to 
                             && booking.BookingLine.Any(b => b.BookingLineSpecification
                                                        .Any(c => c.CurrencyCode == code))
                            )

                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineSpecification)
                         .ThenInclude(bls => bls.UnitType)
                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineAddress)
                         .ThenInclude(bla => bla.Country)
                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineAddress)
                         .ThenInclude(bla => bla.PostalCode)
                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineSpecification)
                         .ThenInclude(bls => bls.RelBookingLineSpecificationSalesInvoiceDetail)
                         .ThenInclude(Rel => Rel.SalesInvoiceDetail);

在 MSSQL 服务器上暂停的 SQL 查询本身变为:

     (@__GetCustomerTarget_Code_0 bigint,@__from_1 datetime2(7),@__to_2 datetime2(7),@__code_3 varchar(255))
        SELECT [booking].[Id], 
    [booking].[booking_provider_id], 
    [booking].[booking_status_id], 
    [booking].[consignee_name], 
    [booking].[consignee_number], 
    [booking].[created_at], 
    [booking].[created_by], 
    [booking].[currency_code], 
    [booking].[deliveryNumber], 
    [booking].[description], 
    [booking].[destroyed_at], 
    [booking].[destroyed_by], 
    [booking].[inter_company_number], 
    [booking].[invoicee_name], 
    [booking].[invoicee_number], 
    [booking].[is_create], 
    [booking].[location_id], 
    [booking].[location_name], 
    [booking].[maturity_level_id], 
    [booking].[number], 
    [booking].[order_number], 
    [booking].[provider_key], 
    [booking].[shipment_id], 
    [booking].[system_responsible_id], 
    [booking].[updated_at], 
    [booking].[updated_by]  
    FROM [Integration].[booking] AS [booking]  
WHERE ((([booking].[consignee_number] = @__GetCustomerTarget_Code_0) 
AND ([booking].[created_at] >= @__from_1)) 
AND ([booking].[created_at] < @__to_2)) 
AND EXISTS (      
SELECT 1      
FROM [Integration].[booking_line] AS [b]      
WHERE EXISTS (          
SELECT 1          
FROM [Integration].[booking_line_specification] AS [c]          
WHERE ([c].[currency_code] = @__code_3) AND ([b].[Id] = [c].[booking_line_id])) AND ([booking].[Id] = [b].[booking_id]))

当在 MSSQL 管理工作室中执行时，此语句在零秒(但几毫秒)内执行。但是，C# 应用程序遇到超时。

当我在 MSSQL 上使用内部工具时，我可以看到 spid 已暂停，并且一直在等待。然而，原因似乎正在改变。一开始是由于 IO_COMPLETION。然后是 SOS_YIELD_~something，最后是 PAGEIOLATCH_SH它保持的最终状态

我这辈子都想不通为什么 MSSQL 可以毫无问题地执行查询。但 EF 似乎无法利用索引。或者我完全想念的其他东西。

我完全没有想法。任何人都可以指出我可能有帮助的方向吗？

我试过:

在 Visual stuido 2017 中运行。以 Release模式运行。我试过启用懒惰模式，而不是使用包含。我已经尝试删除延迟加载和包含，只是为了看看我是否可以取回预订。

没有。 MSSQL 似乎拒绝 EF Core 使用索引。

问题是，只有在我提供某些参数时查询才会挂起。其他参数工作得很好。具体来说，如果我提供不同的货币代码，这似乎会对 MSSQL 的查询是否暂停产生重大影响。

我已经根据 MSSQL management studio 中的执行计划完全重建了高效执行此查询所需的索引。

如果需要任何进一步的信息，请告诉我，我会尽力而为。

更新实际执行计划:

更新 2:我想指出，这目前正在用于开发，因此这个数据库、我的软件以及介于两者之间的任何东西都在我的“控制”之下。

就我显然缺乏经验的头脑来说可以控制任何事情:)

因此，任何关于如何更好地调试问题的建议，或对更多数据的请求都会得到积极和赞赏。并且很可能是可能的，特别是如果提示如何将其提供给您! (还有我)

SQL 事件探查器:打开与数据库的连接:

set quoted_identifier on
set arithabort on
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

Answer 1

这是我们对任何想知道我们为解决此问题所做的工作的人的回答。解决这个事实是相当“黑客”的， Entity Framework 核心实际上并没有让你通过设计来做到这一点，或者通过设计本身来做到这一点。

我们在实际执行请求此特定数据的 C# 代码之前执行此存储过程。

这会查找缓存的执行计划，这些计划可用于看起来很像 EF 生成的查询。然后它一个一个地杀死他们。

这不是一件好事。1、它需要用户有权限才能做到这一点。任何应用程序都不应具有此权限级别。您可以随心所欲地争辩说它“可以拥有”，但实际上，你们都知道它不应该拥有。2、
在代码中，我在 select 语句之前执行存储过程以使应用程序执行。真的。真的吗？嗯，是。我是。整齐吗？不。干净吗？不。我什至不建议这样做，因为这有很多可能的陷阱，如果您更改参数或以任何方式更改查询，则必须重建“like”字符串。我首先通过使用 SQL Profiler 嗅探查询来构建它，然后将尽可能独特的位复制到其他可能的查询执行中。会不会是识别错误？是的。如果您每秒运行 select 一百万次，这可能不是一个可行的解决方案。等等

但这对我有用。

EF 核心，在存储过程中执行查询。但如果您使用存储过程，则不适用于包含。我的大脑因试图绕过这个而流血。所以这个痛苦的解决方案就是我正在使用的解决方案。

/****** Object:  StoredProcedure [dbo].[WipePlanForDraftLoad]    Script Date: 11/01/2019 11:50:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Morten Bork, Henrik Davidsen    
-- Create date: 2019-01-11
-- Description: Procedure to Wipe a cached plan for a query that sort of matches a specific string
-- =============================================
CREATE PROCEDURE [dbo].[WipePlanForDraftLoad]
    -- Add the parameters for the stored procedure here

AS
BEGIN

declare plans cursor 
for 
select t1.plan_handle from sys.dm_exec_cached_plans t1
left outer join sys.dm_exec_query_stats t2 on t1.plan_handle = t2.plan_handle
outer apply sys.dm_exec_sql_text(sql_Handle) 
where text like '%(@__customerCode_0 bigint,@__from_1 datetime2(7),@__to_2 datetime2(7),@__currencyCode_3 varchar(255))%WHERE (\[c\].\[currency_code\] = @__currencyCode_3) AND (\[b\].\[Id\] = \[c\].\[booking_line_id\])) AND (\[booking\].\[Id\] = \[b\].\[booking_id\])%' escape '\'

declare @plan_handle varbinary(64)
open plans
fetch next from plans into @plan_handle
while @@FETCH_STATUS = 0
begin
    dbcc freeproccache(@plan_handle)
    fetch next from plans into @plan_handle
end
close plans
deallocate plans
END
GO