LINQ SQL语句返回错误结果

Question

我有一条SQL语句，afaik正确，但来自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

那么已经取代了它，一个区别是@p5=0当您在其他[t3].[Status] = 5。

Answer 2

您在替换变量时发生了错误！
您提供4个取代P4时，你应该用5和p5与5而不是0