LINQ SQL 语句返回错误结果

Question

我有一个 SQL 语句，据我所知是正确的，但 SQL 服务器的响应不正确。我已经调试了这个问题，发现如果我在没有包装存储过程的情况下执行 SQL 语句，我会得到不同的结果。我所做的就是用 Linq 生成的代码的实际值替换变量

：

exec sp_executesql N'SELECT [t0].[RoomId], [t0].[Title], [t0].[Detail], [t0].[ThumbnailPath], [t0].[PageId], [t0].[TypeId], [t0].[LocationId], [t0].[TimeStamp], [t0].[DeleteStamp]
FROM [dbo].[Room] AS [t0]
INNER JOIN [dbo].[RoomType] AS [t1] ON [t1].[RoomTypeId] = [t0].[TypeId]
WHERE ([t1].[Sleeps] >= @p0) AND ([t0].[DeleteStamp] IS NULL) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Booking] AS [t2]
    INNER JOIN [dbo].[Order] AS [t3] ON [t3].[OrderId] = [t2].[OrderId]
    WHERE ([t2].[StartStamp] <= @p1) 
    AND ([t2].[EndStamp] >= @p2) 
    AND (([t3].[Status] = @p3) 
        OR ([t3].[Status] = @p4) 
        OR (([t3].[Status] = @p5) AND ([t3].[CreatedStamp] > @p6))) 
        AND ([t2].[RoomId] = [t0].[RoomId])
    )) = @p7)


    ',N'@p0 int,@p1 datetime,@p2 datetime,@p3 int,@p4 int,@p5 int,@p6 datetime,@p7 int',
    @p0=1,@p1='2011-04-05 00:00:00',@p2='2011-04-04 00:00:00',@p3=3,@p4=5,@p5=0,@p6='2011-04-04 12:36:09.490',@p7=0

没有 SP

SELECT [t0].[RoomId], [t0].[Title], [t0].[Detail], [t0].[ThumbnailPath], [t0].[PageId], [t0].[TypeId], [t0].[LocationId], [t0].[TimeStamp], [t0].[DeleteStamp]
FROM [dbo].[Room] AS [t0]
INNER JOIN [dbo].[RoomType] AS [t1] ON [t1].[RoomTypeId] = [t0].[TypeId]
WHERE ([t1].[Sleeps] >= 1) AND ([t0].[DeleteStamp] IS NULL) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Booking] AS [t2]
    INNER JOIN [dbo].[Order] AS [t3] ON [t3].[OrderId] = [t2].[OrderId]
    WHERE ([t2].[StartStamp] <= '2011-04-05 00:00:00') 
    AND ([t2].[EndStamp] >= '2011-04-04 00:00:00') 
    AND (([t3].[Status] = 3) 
        OR ([t3].[Status] = 4) 
        OR (([t3].[Status] = 5) AND ([t3].[CreatedStamp] > '2011-04-04 12:36:09.490'))) 
        AND ([t2].[RoomId] = [t0].[RoomId])
    )) = 0)

第一个结果集返回 1 行，而第二个结果集返回 21！

谁能看出其中的区别，因为它让我发疯。

Answer 1

您替换变量时出错！
当您应该将 p4 替换为 5 时，您将 p4 替换为 4，将 p5 替换为 5 而不是 0。

Answer 2

好吧，其中一个区别是 @p5=0，而另一个区别是 [t3].[Status] = 5。