LINQ SQL Statement is returning wrong results

Question

I have a SQL statement which afaik is correct but the response from the SQL server is incorrect. I've debugged this issue and found that if I execute the SQL statement without the wrapping store procedure I get different results. All I have done is replaced the variable with the actual values

Linq generated code:

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

Without the 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)

The first result set returns 1 row where as the 2nd returns me 21!!

Can anybody spot the difference as its driving me crazy.

Why are you bothing with the SQL code generated from a LINQ query? Show us the LINQ query and maybe we can find the error in it. — tster
– tster, Commented Apr 4, 2011 at 12:37

Daniel Hilgarth · Accepted Answer · 2011-04-04 12:37:12Z

2

You made an error replacing the variables!
You replaced p4 with 4 when you should have replaced it with 5 and p5 with 5 instead of 0.

answered Apr 4, 2011 at 12:37

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aliostad · Accepted Answer · 2011-04-04 12:37:21Z

1

Well, one difference is @p5=0 while you have [t3].[Status] = 5 in the other.

answered Apr 4, 2011 at 12:37

Aliostad

81.9k21 gold badges164 silver badges209 bronze badges

Collectives™ on Stack Overflow

LINQ SQL Statement is returning wrong results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related