.net - EF6 生成的 SQL 是无效的 SQL 服务器端
问题描述
这听起来很疯狂——但我观察到的事实如下：我有一个使用 EF6 连接到 SQL 服务器的 apsx mvc 应用程序。II 有时会在底层生成的 SQL 上遇到异常。喜欢：
关键字 'AS' 附近的语法不正确”和“'[Extend1]' 附近的语法不正确
（以及这个的变体）
在我看来，这不应该发生在像 EF 这样的框架中。
幸运的是，我引入了一个 SQL 拦截器（如https://docs.microsoft.com/en-us/ef/ef6/fundamentals/logging-and-interception），让我有机会在执行之前查看 SQL。我的第一步是检查生成的 SQL - 这看起来很正常。在另一个步骤中，我在 SQL 服务器上附加了一个分析器！你猜怎么着——截获日志的 SQL 与 profiler 中记录的 SQL 不匹配。（听起来 fubar，但它是观察者）到目前为止的结论是：SQL 中的单个字符不时发生变化（在观察到的情况下，逗号变成了换页符。）
互联网上是否有人知道这可能是什么背景？
更新1：（要求的信息）
=== EF 代码 ===
public Customer GetById(string id)
{
return DbContext.Customers.Include("Address").FirstOrDefault(a => a.Id == id);
}
=== 来自拦截器 ===
2019-11-06 09:07:14.471 +01:00 [Interceptors.SqlLogInterceptor] [44] [<<username>>] [Debug] SELECT
[Limit1].[AddressTypeId] AS [AddressTypeId],
[Limit1].[Id] AS [Id],
[Limit1].[AddressId] AS [AddressId],
[Limit1].[ShowPrebooking] AS [ShowPrebooking],
[Limit1].[Id1] AS [Id1],
[Limit1].[Name1] AS [Name1],
[Limit1].[Name2] AS [Name2],
[Limit1].[Address1] AS [Address1],
[Limit1].[Address2] AS [Address2],
[Limit1].[HouseNumber] AS [HouseNumber],
[Limit1].[PostCode] AS [PostCode],
[Limit1].[City] AS [City],
[Limit1].[State] AS [State],
[Limit1].[CountryCode] AS [CountryCode],
[Limit1].[ContactName] AS [ContactName],
[Limit1].[Email] AS [Email],
[Limit1].[Phone] AS [Phone],
[Limit1].[Mobile] AS [Mobile],
[Limit1].[CreatedByUserId] AS [CreatedByUserId],
[Limit1].[CreatedDateTime] AS [CreatedDateTime],
[Limit1].[UpdatedByUserId] AS [UpdatedByUserId],
[Limit1].[UpdatedDateTime] AS [UpdatedDateTime],
[Limit1].[DeliveryDescription] AS [DeliveryDescription],
[Limit1].[OpeningHoursTo] AS [OpeningHoursTo],
[Limit1].[OpeningHoursFrom] AS [OpeningHoursFrom]
FROM ( SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[AddressId] AS [AddressId],
[Extent1].[ShowPrebooking] AS [ShowPrebooking],
[Extent2].[Id] AS [Id1],
[Extent2].[Name1] AS [Name1],
[Extent2].[Name2] AS [Name2],
[Extent2].[Address1] AS [Address1],
[Extent2].[Address2] AS [Address2],
[Extent2].[HouseNumber] AS [HouseNumber],
[Extent2].[PostCode] AS [PostCode],
[Extent2].[City] AS [City],
[Extent2].[State] AS [State],
[Extent2].[CountryCode] AS [CountryCode],
[Extent2].[ContactName] AS [ContactName],
[Extent2].[Email] AS [Email],
[Extent2].[Phone] AS [Phone],
[Extent2].[Mobile] AS [Mobile],
[Extent2].[AddressTypeId] AS [AddressTypeId],
[Extent2].[CreatedByUserId] AS [CreatedByUserId],
[Extent2].[CreatedDateTime] AS [CreatedDateTime],
[Extent2].[UpdatedByUserId] AS [UpdatedByUserId],
[Extent2].[UpdatedDateTime] AS [UpdatedDateTime],
[Extent2].[DeliveryDescription] AS [DeliveryDescription],
[Extent2].[OpeningHoursTo] AS [OpeningHoursTo],
[Extent2].[OpeningHoursFrom] AS [OpeningHoursFrom]
FROM [dbo].[Customers] AS [Extent1]
INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[AddressId] = [Extent2].[Id]
WHERE [Extent1].[Id] = @p__linq__0
) AS [Limit1]
2019-11-06 09:07:14.471 +01:00 [Yourleman.Ef.Interceptors.SqlLogInterceptor] [44] [<<username>>] [Debug] -- Failed in 1 ms with error: Incorrect syntax near 'Limit1'.
Incorrect syntax near the keyword 'AS'.
=== 来自分析器的跟踪 ===
exec sp_executesql N'SELECT
[Limit1].[AddressTypeId] AS [AddressTypeId],
[Limit1].[Id] AS [Id],
[Limit1].[AddressId] AS [AddressId],
[Limit1].[ShowPrebooking] AS [ShowPrebooking],
[Limit1].[Id1] AS [Id1],
[Limit1].[Name1] AS [Name1],
[Limit1].[Name2] AS [Name2],
[Limit1].[Address1] AS [Address1],
[Limit1].[Address2] AS [Address2],
[Limit1].[HouseNumber] AS [HouseNumber],
[Limit1].[PostCode] AS [PostCode],
[Limit1].[City] AS [City],
[Limit1].[State] AS [State],
[Limit1].[CountryCode] AS [CountryCode],
[Limit1].[ContactName] AS [ContactName],
[Limit1].[Email] AS [Email],
[Limit1].[Phone] AS [Phone],
[Limit1].[Mobile] AS [Mobile],
[Limit1].[CreatedByUserId] AS [CreatedByUserId],
[Limit1].[CreatedDateTime] AS [CreatedDateTime],
[Limit1].[UpdatedByUserId] AS [UpdatedByUserId],
[Limit1].[UpdatedDateTime] AS [UpdatedDateTime],
[Limit1].[DeliveryDescription] AS [DeliveryDescription],
[Limit1].[OpeningHoursTo] AS [OpeningHoursTo]
[Limit1].[OpeningHoursFrom] AS [OpeningHoursFrom]
FROM ( SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[AddressId] AS [AddressId],
[Extent1].[ShowPrebooking] AS [ShowPrebooking],
[Extent2].[Id] AS [Id1],
[Extent2].[Name1] AS [Name1],
[Extent2].[Name2] AS [Name2],
[Extent2].[Address1] AS [Address1],
[Extent2].[Address2] AS [Address2],
[Extent2].[HouseNumber] AS [HouseNumber],
[Extent2].[PostCode] AS [PostCode],
[Extent2].[City] AS [City],
[Extent2].[State] AS [State],
[Extent2].[CountryCode] AS [CountryCode],
[Extent2].[ContactName] AS [ContactName],
[Extent2].[Email] AS [Email],
[Extent2].[Phone] AS [Phone],
[Extent2].[Mobile] AS [Mobile],
[Extent2].[AddressTypeId] AS [AddressTypeId],
[Extent2].[CreatedByUserId] AS [CreatedByUserId],
[Extent2].[CreatedDateTime] AS [CreatedDateTime],
[Extent2].[UpdatedByUserId] AS [UpdatedByUserId],
[Extent2].[UpdatedDateTime] AS [UpdatedDateTime],
[Extent2].[DeliveryDescription] AS [DeliveryDescription],
[Extent2].[OpeningHoursTo] AS [OpeningHoursTo],
[Extent2].[OpeningHoursFrom] AS [OpeningHoursFrom]
FROM [dbo].[Customers] AS [Extent1]
INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[AddressId] = [Extent2].[Id]
WHERE [Extent1].[Id] = @p__linq__0
) AS [Limit1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'2341234'
检查字节显示 [OpeningHoursTo] 之后有一个换页符 (\u000C)
解决方案
瞧，在 2 月 3 日，错误突然返回。我们生产服务器的所有流量似乎都受到了影响，我们的客户会遇到随机错误。
在托管网站的服务器上使用日志文件和 Wireshark，我们可以排除该特定服务器，因为 TCP 包中的数据是正确的。我们还可以排除数据库服务器和另一个应用程序服务器，因为对两个服务器的请求都会以同样的方式失败。
最后，Infrastructure 能够将问题与网络中交换机上的故障端口隔离开来。禁用此端口后，问题立即得到解决。