EFLINQtoSQL，除以零錯誤，生成的查詢將引數的順序錯誤-有解無憂

編輯在帖子底部重現此錯誤的步驟

我的這個問題的資料結構：

    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 中修復它？我沒有在 EF 查詢中將 0 放在首位，而且我不記得以前發生過這種情況，這是我一直用來確保我沒有試圖除以零并且它曾經作業的解決方案。我知道我可以手動撰寫 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)

Run the query again. Divide by zero error happens. After playing with the data, this behavior makes sense. If the @stockYear is greater than or less than the StartYear and the interval of that record is zero, it will error out because if gets to the inner most part of the query, and the interval is zero and it doesn't have boolean expression shortcutting. Okay.

But switch the one line of the query to:

StockIntervals.[IntervalInYears] <> 0

Now it works! Not sure how this is coincidence though, I've run my scripts through many scenarios to trigger the error, but it always is resolved by the above. If there is no short cutting, switching the operands should still cause the error. Yet it does not, consistently. So people are saying the operand order doesn't matter, but I am able to show it appears to.

uj5u.com熱心網友回復：

您似乎在假設AND和OR在 T-SQL 中將始終按照查詢中指定的順序短路。絕對不是這樣。

確實，它通常會使邏輯運算式短路。畢竟，為什么要做比必要的更多的作業？但它可能與查詢中指定的順序不同。邏輯運算子未指定以任何特定順序執行，優化器通常會根據短路可能性的估計或評估中涉及的作業量等因素選擇切換它們，只要遵循運算子優先規則（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 上運行的這個 fiddle中看到的那樣，您的兩個選項都正確短路，翻轉AND. 我不得不翻轉OR它以使其失敗，然后的順序AND無關緊要。請注意，任何查詢中的邏輯都沒有改變，AND 或者 =比較器本身的順序并不強制優化器的手，它有時只是引導它沿著特定的路徑前進。

所以它非常依賴于優化器決定做什么，你不能預先保證它總是能正確地做。是的，你已經看到它這樣做了一百次，但是第一百次可能會改變，可能是因為統計資訊的變化，或者 SQL Server 的更新，或者改變了基數估計器的版本，或者資料庫的兼容性級別，或者任何一個許多可能導致重新編譯的事情。

確保以特定順序短路的唯一有保證的方法CASE是使用（或NULLIF編譯成 a CASE）。這由 Microsoft 記錄，只要您不使用任何聚合函式，它就會起作用。

換句話說，不要期望類似的東西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) 
  )
);

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/410692.html

標籤：

上一篇：如果使用物體框架（DBFirst）（ASP.NETMVC）的記錄中已經存在唯一欄位的組合，則阻止發布物件

下一篇：無法創建物體框架代碼優先遷移