Looking at the Oracle support, it's similar but nowhere near identical
to what SQL Server would require.
OracleSqlProvider.GetLiteralStatement() is:
return SqlStatement.Format(
@"SELECT * FROM ({0}) WHERE {2} > {1}",
GetLiteralLimit(select, offsetAndLimit), offset, LimitedRownum,
NewLine);
return base.GetLiteralLimit(select, limit, offset, offsetAndLimit);
For example, for the original SQL 'SELECT Foo FROM Customers ORDER BY
Foo', then Oracle would generate the new SQL 'SELECT * FROM (SELECT
LimitedTable___.*, rownum Limit___ FROM (Foo FROM Customers ORDER BY
Foo) WHERE rownum <= `limit`) WHERE Limit___ > offset'.
So first of all, SQL Server doesn't seem to have many of these
extensions like Limit___, and secondly SQL Server defined it's
SqlServerSqlProvider.GetLiteralLimit(SqlStatement, SqlStatement) method
to use the TOP function, e.g. 'SELECT TOP(10) Foo FROM Customers ORDER
BY Foo'. I don't think that the TOP function can be abused to provide
paging services (which is presumably why System.Data.Linq doesn't
generate TOP here.
That said, my original question was, "Is there a higher-level interface
exposed to SQL Vendors to control SQL generation?" The answer is
apparently No, which is why the SQL Vendor code is full of string
manipulations. :-(
Thanks,
- Jon
On Tue, 2009-05-05 at 08:47 +0200, Pascal Craponne wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
