Hi Jon, At a time, the paging support for Oracle was working (it may still work, I haven't checked), and it is written just like you would do for SQL server.
Pascal. On Mon, May 4, 2009 at 23:12, Jonathan Pryor <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
