我有一个LINQWhere
子句,它在SQL查询上声明了两个变量
var parkingLotPrice =
_context.ParkingLotPrice
.Where(x => currentDate >= x.EffectiveDate && (currentDate <= x.ExpiryDate || x.ExpiryDate == null))
.ToQueryString();
它生成以下SQL查询:
DECLARE @__currentDate_0 datetime2 = '2021-07-21T17:48:29.1106534-06:00';
DECLARE @__currentDate_1 datetime2 = '2021-07-21T17:48:29.1106534-06:00';
SELECT [p].[ParkingLotId],
[p].[PriceScheduleId],
[p].[EffectiveDate],
[p].[ExpiryDate]
FROM [ParkingLotPrice] AS [p]
WHERE (@__currentDate_0 >= [p].[EffectiveDate]) AND ((@__currentDate_1 <= [p].[ExpiryDate]) OR [p].[ExpiryDate] IS NULL)
注意:声明包含相同的值。
问题出在(currentDate <= x.ExpiryDate || x.ExpiryDate == null)
上。
如果删除null
求值,它只声明1个变量。
DECLARE @__currentDate_0 datetime2 = '2021-07-21T17:32:31.3980763-06:00';
SELECT [p].[ParkingLotId],
[p].[PriceScheduleId],
[p].[EffectiveDate],
[p].[ExpiryDate]
FROM [ParkingLotPrice] AS [p]
WHERE (@__currentDate_0 >= [p].[EffectiveDate]) AND (@__currentDate_0 <= [p].[ExpiryDate])
有没有一种方法可以保持Where
的求值,但只声明1个变量?
它有什么问题?它是否返回错误的结果?我创建了数百个带null和不带null的查询,它们总是返回正确的结果。
我能从你的询问中看出真正的问题。你比较日期的方式。它也会比较时间。在某些情况下,它会返回错误的结果。我强烈建议你只比较的日期
var parkingLotPrice = _context.ParkingLotPrice
.Where(x => EF.Functions.DateDiffDay(x.EffectiveDate,currentDate) >=0
&& (x.ExpiryDate == null || EF.Functions.DateDiffDay(currentDate,x.ExpiryDate)>=0 )).ToList();
或者如果你需要一些时间,试试这个
var parkingLotPrice = _context.ParkingLotPrice
.Where(x => EF.Functions.DateDiffMinute(x.EffectiveDate,currentDate) >=0
&& (x.ExpiryDate == null || EF.Functions.DateDiffMinute(currentDate,x.ExpiryDate)>=0 )).ToList();