google-code-export / dblinq2007

Automatically exported from code.google.com/p/dblinq2007
Other
0 stars 0 forks source link

SqlServer GetLiteralLimit corrupts query if it query consists of multiple parts #330

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
What steps will reproduce the problem?
1. Create query with where statement, in which a variable is used
2. Add Skip() and Take() to force calling GetLiteralLimit method
3. Print resultant query

What is the expected output? What do you see instead?
EXPECTED:

SELECT *
FROM (
    SELECT [field1], [field2]
    ROW_NUMBER() OVER(ORDER BY [field1], [field2]
) AS [__ROW_NUMBER]
    FROM [dbo].[Table]
WHERE ([field1] LIKE @variable)    ) AS [t0]
WHERE [__ROW_NUMBER] BETWEEN 0+1 AND 0+10
ORDER BY [__ROW_NUMBER]

INSTEAD:
SELECT *
FROM (
    SELECT [field1], [field2]
    ROW_NUMBER() OVER(ORDER BY [field1], [field2]
) AS [__ROW_NUMBER]
    FROM [dbo].[Table]
WHERE ([field1] LIKE     ) AS [t0]
WHERE [__ROW_NUMBER] BETWEEN 0+1 AND 0+10
ORDER BY [__ROW_NUMBER]

-- Note That after LIKE everything is missing from the original query

What version of the product are you using? On what operating system?
DbLinq 0.19
Ubuntu Linux OS

Please provide any additional information below.

I have traced the problem to GetLiteralLimit function in svn/  trunk/ src/ 
DbLinq.SqlServer/ SqlServerSqlProvider.cs

It recieves SqlStatement select, but instead of using the whole resulting query 
only uses select[0].Sql
In my example select[0] would be whole query till first @variable, and the 
@variable itself is in select[1] which is just never used in returned statement.

Easy fix is replace every "select[0].Sql" with "select.ToString()" then the 
result is correct.

Original issue reported on code.google.com by kris...@gmail.com on 7 Mar 2012 at 2:28