What the heck is happening with this Linq-to-SQL query?

[Resolved - see update]

I have this L2S query (looks scary, but the bit that's going wrong is simple):

var historicDataPointValues = from dpv in this._dataContext.DataPointValues
                              where (dpv.categoryId == historicCategoryId)
                              && (dpv.retailerId == historicRetailerId)
                              && (dpv.questionId == question.PreviousQuestionId)
                              && historicResponseIds.Contains(dpv.responseIndex)
                              && ((dpv.dataPointIndex == firstDataPointIndex) || (dpv.dataPointIndex == secondDataPointIndex))
                              select new KeyValuePair<string, double>(MakeDataPointValueKey(dpv), dpv.value);

...which was producing no results, so I looked at the SQL emitted by L2S.

When I execute it, the value of firstDataPointIndex is 1 and secondDataPointIndex is null. Yet the query that L2S produces (as shown in the debugger) is:

SELECT [t0].[categoryId], [t0].[retailerId], [t0].[questionId], [t0].[responseIndex], [t0].[dataPointIndex], [t0].[value]
FROM [dbo].[DataPointValue] AS [t0]
WHERE ([t0].[categoryId] = 1)
AND ([t0].[retailerId] = 1)
AND (([t0].[questionId]) = 8)
AND ([t0].[responseIndex] IN (1, 3, 4, 5, 6, 7, 8, 10, 11, 12))
AND (([t0].[dataPointIndex] = 13) OR ((CONVERT(Int,[t0].[dataPointIndex])) = 14))

Note the values 13 & 14 where firstDataPointIndex and secondDataPointIndex should be (they should be 1 and NULL; if I put in the correct values, then I get the results I expect).

WTF?

UPDATE: Turns out that the L2S debugger was at fault. It wasn't showing the same SQL as what's actually sent to the server, which is:

exec sp_executesql N'SELECT [t0].[categoryId], [t0].[retailerId], [t0].[questionId], [t0].[responseIndex], [t0].[dataPointIndex], [t0].[value]
FROM [dbo].[DataPointValue] AS [t0]
WHERE ([t0].[categoryId] = @p0) AND ([t0].[retailerId] = @p1) AND (([t0].[questionId]) = @p2) AND ([t0].[responseIndex] IN (@p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12)) AND (([t0].[dataPointIndex] = @p13) OR ((CONVERT(Int,[t0].[dataPointIndex])) = @p14))',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int,@p10 int,@p11 int,@p12 int,@p13 int,@p14 int',@p0=1,@p1=1,@p2=8,@p3=1,@p4=3,@p5=4,@p6=5,@p7=6,@p8=7,@p9=8,@p10=9,@p11=10,@p12=11,@p13=1,@p14=NULL

Note that the query now contains @p13 and @p14 where previously it had scalar values.

ansaurus

tags:

views:

answers:

What the heck is happening with this Linq-to-SQL query?

related questions