I'm trying to fix the Paging.LinqToSqlPaging01() test for SQL Server.
The reason it fails, unsurprisingly, is because the generated SQL is
completely wrong for SQL Server. It's currently:
SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle],
[Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]
FROM [dbo].[Customers]
ORDER BY [ContactName] LIMIT 2 OFFSET 1
For the same test, SQL Server generates:
SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName],
[t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region],
[t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ContactName]) AS
[ROW_NUMBER],
[t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City],
[t0].[Region],
[t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
i.e. it uses a nested sub-select and the ROW_NUMBER feature in order to
specify the offset.
The question: how do I best to support this in DbLinq?
Given the current architecture, the solution appears to be to override
SqlProvider.GetLiteralLimit(SqlStatement, SqlStatement, SqlStatement,
SqlStatement) within SqlServerSqlProvider. My problem with this is that
it would need to be entirely string based, as the select parameter is
the entire 'SELECT [CustomerID] ...' command is a single string,
resulting in an implementation equivalent to:
int fromIdx = select[0].Sql.IndexOf("FROM");
string outerSelect = select[0].Sql.Substring(0, fromIdx).Replace("[",
"[t0].[");
return SqlStatement.Format("SELECT {0} FROM ({1}) AS [t0] WHERE
[t0].[ROW_NUMBER] BETWEEN ...",
outerSelect, select[0].Sql, ...);
I don't consider this to be ideal, as if we have e.g. nested selects
(db.Where(...).Skip(1).Take(10).Skip(3).Take(2)) I suspect things would
break (as we'd get multiple [t0] tables defined).
Is there a better solution? Or is string-based manipulation the only
way to go?
Thanks,
- Jon
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"DbLinq" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/dblinq?hl=en
-~----------~----~----~----~------~----~------~--~---
