none
Problem with more than 10 parameters in generated T-SQL RRS feed

  • Question

  • Hello

    i'm experiencing a weird problem when using LINQ to SQL. I've set up a more or less complicated query to compare two dates from parameters to x rows in a table. The last parameter is replaced with it's wrong equivalent when the query is converted to T-SQL.

    LINQ
    REMARK: endDate and startDate are DateTime objects, fkObject is an Integer. vac.enddate and vac.startdate are datetime-typed Columns.

    var check = from vac in db.objVac 
    where 
        (endDate < vac.enddate && startDate > vac.startdate) || 
        (endDate > vac.enddate && startDate > vac.startdate && startDate < vac.enddate) || 
        (startDate < vac.startdate && endDate < vac.enddate && endDate > vac.startdate) || 
        (startDate < vac.startdate && endDate > vac.enddate) 
    ) && vac.fkObject == fkObject 
    select vac; 


    Generated Original Query (copy&paste from LINQ to SQL Visualizer)

    REMARK: bold highlights are relevant for next section

    SELECT [t0].[id], [t0].[fkSeason], [t0].[fkObject], [t0].[startdate], [t0].[enddate]
    FROM [dbo].[objVac] AS [t0]
    WHERE (((@p0 < [t0].[enddate]) AND (@p1 > [t0].[startdate])) OR ((@p2 > [t0].[enddate]) AND (@p3 > [t0].[startdate]) AND (@p4 < [t0].[enddate])) OR ((@p5 < [t0].[startdate]) AND (@p6 < [t0].[enddate]) AND (@p7 > [t0].[startdate])) OR ((@p8 < [t0].[startdate]) AND (@p9 > [t0].[enddate]))) AND ([t0].[fkObject] = @p10)
    -------------------------------
    @p0 [DateTime]: 18.07.2009 00:00:00
    @p1 [DateTime]: 04.07.2009 00:00:00
    @p2 [DateTime]: 18.07.2009 00:00:00
    @p3 [DateTime]: 04.07.2009 00:00:00
    @p4 [DateTime]: 04.07.2009 00:00:00
    @p5 [DateTime]: 04.07.2009 00:00:00
    @p6 [DateTime]: 18.07.2009 00:00:00
    @p7 [DateTime]: 18.07.2009 00:00:00
    @p8 [DateTime]: 04.07.2009 00:00:00
    @p9 [DateTime]: 18.07.2009 00:00:00
    @p10 [Int32]: 222

    Final Query (sent to SQL server)

    REMARKS: obviously, LINQ to SQL mixes up two parameters here:@p1 and @p10. It's replacing @p10 with @p1'0' instead of using the Integer value.

    SELECT [t0].[id], [t0].[fkSeason], [t0].[fkObject], [t0].[startdate], [t0].[enddate]
    FROM [dbo].[objVac] AS [t0]
    WHERE ((('18.07.2009 00:00:00' < [t0].[enddate]) AND ('04.07.2009 00:00:00' > [t0].[startdate])) OR (('18.07.2009 00:00:00' > [t0].[enddate]) AND ('04.07.2009 00:00:00' > [t0].[startdate]) AND ('04.07.2009 00:00:00' < [t0].[enddate])) OR (('04.07.2009 00:00:00' < [t0].[startdate]) AND ('18.07.2009 00:00:00' < [t0].[enddate]) AND ('18.07.2009 00:00:00' > [t0].[startdate])) OR (('04.07.2009 00:00:00' < [t0].[startdate]) AND ('18.07.2009 00:00:00' > [t0].[enddate]))) AND ([t0].[fkObject] = '04.07.2009 00:00:00'0)

    Does anybody have an explanation for this behavior?


    Tuesday, February 10, 2009 9:28 AM

All replies

  • LINQ to SQL does not actually embed the text of values in the translated SQL.  Are you using the sample debug visualizer to get this text?  I recall there was such a bug in the sample that mishandled parameters over 10.


    Wayward LINQ Lacky
    Tuesday, February 10, 2009 4:16 PM
    Moderator