EF LINQ到SQL，除以零错误，生成的查询将参数按错误顺序排列

Question

编辑在后底部重现此错误的步骤

我的这个问题的数据结构：

public class StockRequest
{
public int StartYear { get; set; }
public StockInterval StockInterval { get; set; }
}
public class StockInterval
{
/// <summary>
///  Can be 0 = non-recurring, 1 = annual, 2 = once every 2 years, 3 = once every 3 years
/// </summary>
public int IntervalInYears { get; set; }
}

如果我想获得2021年的所有库存请求。以下数据将符合该标准：

var nonRecurringRequest = new StockRequest() { StartYear = 2021, StockInterval = new StockInterval() { IntervalInYears = 0 } };
var annualRequest = new StockRequest() { StartYear = 2020, StockInterval = new StockInterval() { IntervalInYears = 1 } };
var everyTwoYearsRequest = new StockRequest() { StartYear = 2019, StockInterval = new StockInterval() { IntervalInYears = 2 } };
var everyThreeYearsRequest = new StockRequest() { StartYear = 2018, StockInterval = new StockInterval() { IntervalInYears = 3 } };

EF查询中的关键字where子句是：

query.Where(x => 
x.StartYear <= selectedYear && 
(
x.StartYear == selectedYear || 
(x.StockInterval.IntervalInYears != 0 && selectedYear - x.StartYear % x.StockInterval.IntervalInYears == 0) 
)
);

导致问题的部分是非经常性库存请求(间隔为0)。你不能修改它，因为那样你就除以零。然而，我知道这一点，过去我通过在尝试修改之前首先检查属性(IntervalInYears)是否为零来解决这一问题。由于WHERE的第一部分未通过检查，因此它不会继续到mod部分。

出于某种原因，这次不起作用。当我检查生成的查询时，它将0放在第一位：

WHERE 
StockRequests.[StartYear] <= @stockYear
AND 
(
StockRequests.[StartYear] = @stockYear OR 
(
0 <> StockIntervals.[IntervalInYears] AND 
0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
)
)

在SQL Server中执行该操作会产生除以零的错误。然而，翻转0和StockIntervals.IntervalInYears:

WHERE 
StockRequests.[StartYear] <= @stockYear
AND 
(
StockRequests.[StartYear] = @stockYear OR 
(
StockIntervals.[IntervalInYears] <> 0  AND 
0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
)
)

现在它没问题了。为什么EF会改变这种情况，我如何在EF中解决它？我没有把0放在EF查询的第一位，我也不记得以前发生过这种情况，这是我一直用来确保我没有试图除以零的解决方案，而且它曾经有效。我知道我可以手动编写SQL查询并执行它，但投影超过200行。

编辑要复制：表创建脚本：

CREATE TABLE [dbo].[StockIntervals](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[IntervalInYears] [int] NOT NULL,
CONSTRAINT [PK_dbo.StockIntervals] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StockIntervals] ADD  DEFAULT ((0)) FOR [IntervalInYears]
GO
CREATE TABLE [dbo].[StockRequests](
[Id] [uniqueidentifier] NOT NULL,
[Count] [int] NOT NULL,
[DateRequested] [datetime] NOT NULL,
[StartYear] [int] NOT NULL,
[StockIntervalId] [uniqueidentifier] NOT NULL,
[EndYear] [int] NULL,

CONSTRAINT [PK_dbo.StockRequests] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StockRequests]  WITH CHECK ADD  CONSTRAINT [FK_dbo.StockRequests_dbo.StockIntervals_StockIntervalId] FOREIGN KEY([StockIntervalId])
REFERENCES [dbo].[StockIntervals] ([Id])
GO
ALTER TABLE [dbo].[StockRequests] CHECK CONSTRAINT [FK_dbo.StockRequests_dbo.StockIntervals_StockIntervalId]
GO

填充表格：

INSERT INTO [dbo].[StockIntervals]
([Id]
,[Name]
,[IntervalInYears])
VALUES
('738A431E-D517-4C17-9ECA-A1A0942E236B', 'Non-recurring one time', 0),
('CCB746A7-F644-4C7E-ADBE-AE14DE01B19E', 'Annual', 1),
('80C6CAE6-5287-41E6-A5FE-AAA53035EC19', 'Every 2 years', 2),
('B34EE256-C40B-4F03-8232-14B681186C7A', 'Every 3 years', 3)
GO
INSERT INTO [dbo].[StockRequests]
([Id]
,[Count]
,[DateRequested]
,[StartYear]
,[StockIntervalId]
,[EndYear])
VALUES
('4a5ae94e-0a85-4195-8e7e-8cc556307b30'
,15
,'2022-01-11 15:16:41.567'
,2021
,'738A431E-D517-4C17-9ECA-A1A0942E236B'
,null),
('f0d83b68-0da1-4824-9eeb-2e52ff369db5'
,60
,'2022-01-11 15:16:41.567'
,2020
,'CCB746A7-F644-4C7E-ADBE-AE14DE01B19E'
,null),
('a49b4b9e-80d6-4fca-ad78-6c8996616c97'
,1000
,'2022-01-11 15:16:41.567'
,2019
,'80C6CAE6-5287-41E6-A5FE-AAA53035EC19'
,null),
('cc21a265-f8df-4d2d-9eae-5f6f97ef9909'
,50
,'2022-01-11 15:16:41.567'
,2018
,'B34EE256-C40B-4F03-8232-14B681186C7A'
,null)
GO

运行此查询：

DECLARE @stockYear int = 2021
SELECT * FROM 
dbo.StockRequests
INNER JOIN dbo.StockIntervals on StockIntervalId = StockIntervals.Id
WHERE 
StockRequests.[StartYear] <= @stockYear
AND 
(
StockRequests.[StartYear] = @stockYear OR 
(
0 <> StockIntervals.[IntervalInYears]  AND 
0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
)
)

没有错误。好的，现在尝试插入一个新记录：

INSERT INTO [dbo].[StockRequests]
VALUES ('FFA820F1-E361-4AC5-AB00-E621BFFEF9B5', 20, '2022-01-11 16:22:11.567', 2020, '738A431E-D517-4C17-9ECA-A1A0942E236B', null)

再次运行查询。发生被零除错误。在玩过数据之后，这种行为是有意义的。如果@stockYear大于或小于StartYear，并且该记录的间隔为零，则它将出错，因为If到达查询的最内部，并且间隔为零并且没有布尔表达式快捷方式。可以

但将查询的一行切换为：

StockIntervals.[IntervalInYears] <> 0

现在它工作了！虽然我不确定这是巧合，但我已经在许多场景中运行了我的脚本来触发错误，但它总是通过上面的方法来解决。如果没有捷径，切换操作数仍然会导致错误。但事实并非如此。所以人们说操作数顺序无关紧要，但我能证明这一点。

Answer 1

您似乎在假设T-SQL中的AND和OR将始终按照查询中指定的顺序短路。事实并非如此

的确，它通常会使逻辑表达式短路。毕竟，为什么要做更多的工作呢？但它可能不符合查询中指定的顺序。逻辑运算符没有被指定以任何特定顺序执行，优化器通常会根据短路可能性的估计或评估中涉及的工作量等因素来选择切换它们，只要遵循运算符优先级规则(AND先于OR等)。

由于评估所有可能的执行计划的空间太大，优化器使用激进的修剪来删除基于启发式的选项。这两个谓词：

(
StockRequests.[StartYear] = @stockYear OR 
(
0 <> StockIntervals.[IntervalInYears]  AND 
0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
)
)

和

(
StockRequests.[StartYear] = @stockYear OR 
(
StockIntervals.[IntervalInYears] <> 0  AND 
0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
)
)

就查询意图而言是完全相同的。问题是优化器将选择如何处理它们。在您的情况下，将比较器单向放置会导致某些优化到位(或不到位)，因此AND可能会发生翻转。

正如您从这个运行在SQL Server 2019上的小提琴中看到的那样，您的两个选项都正确短路，就像翻转AND一样。我不得不翻转OR以使其失败，然后AND的顺序无关紧要。请注意，逻辑在任何查询中都没有改变，AND或=比较器本身的顺序并不会迫使优化器，它只是有时引导它沿着某条路径前进。

因此，这在很大程度上取决于优化器决定做什么，并且您不能预先保证它总是正确地做这件事。是的，您已经看到它这样做了一百次，但第一百零一次可能会更改，可能是因为统计信息的更改，或者SQL Server的更新，或者基数估计器版本的更改，或数据库兼容性级别的更改，也可能是由于许多可能导致重新编译的因素。

确保按特定顺序短路的唯一保证方式是使用CASE(或编译为CASE的NULLIF)。这是由微软记录的，只要你不使用任何聚合函数，它就会起作用。

换句话说，不要期望像CASE WHEN x > 0 THEN SUM(1 / x) END这样的东西起作用，因为SUM通常在早期阶段进行评估它只适用于标量值据我所知，我希望同样的问题也适用于子查询和窗口函数。

因此，您可以使用NULLIF来解决您的问题

(
StockRequests.[StartYear] = @stockYear OR 
(
StockIntervals.[IntervalInYears] <> 0  AND 
0 = (@stockYear - StockRequests.[StartYear]) % NULLIF(StockIntervals.[IntervalInYears], 0)
)
)

在实体框架中，您可以使用类似(value == 0 ? null : value)的东西

query.Where(x => 
x.StartYear <= selectedYear && 
(
x.StartYear == selectedYear || 
(x.StockInterval.IntervalInYears != 0
&& selectedYear - x.StartYear %
(x.StockInterval.IntervalInYears == 0 ? null : x.StockInterval.IntervalInYears)
== 0) 
)
);