DevExpress XPO – a support for LINQ’s Skip is finally there

A lack of support for LINQ’s Skip has been a huge drawback of the XPO implementation. I am glad to confirm that Skip is finally there. I test it with Microsoft SQL Server 2008 where Skip is evaluated using row_number. For example, a simple query:

new XPQuery<D_Grupa_Roli>( Global.Model )

    .Where( o => o.Oid > 0 )

    .OrderBy( o => o.Nazwa )

    .Skip( 2 )

    .Take( 3 )

    .ToList()

    .ForEach( rola => Console.WriteLine( rola.Nazwa ) );

is evaluated as

exec sp_executesql 

   N'select resultSet.F0, resultSet.F1, resultSet.F2, resultSet.F3, resultSet.F4, resultSet.F5, resultSet.F6 

     from(select N0."OID" as F0, N0."ModifyDate" as F1, N0."ModifyUser" as F2, N0."ID_GLOBAL_DICT" as F3, 

                 N0."Nazwa" as F4, N0."OptimisticLockField" as F5, N0."GCRecord" as F6, 

                 row_number() over(order by N0."Nazwa" asc) as ''rowNumber'' 

         from "dbo"."D_Grupa_Roli" N0

         where (N0."GCRecord" is null and (N0."OID" > @p0))) resultSet 

     where resultSet.rowNumber > 2 and resultSet.rowNumber <= 2 + 3',N'@p0 int',@p0=0

What’s interesting is that skipping throws an exception for an unordered query:

new XPQuery<D_Grupa_Roli>( Global.Model )

    .Where( o => o.Oid > 0 )

     //.OrderBy( o => o.Nazwa ) ordering is missing

    .Skip( 2 )

    .Take( 3 )

    .ToList()

    .ForEach( rola => Console.WriteLine( rola.Nazwa ) );

and the exception says “Can not skip records without ORDER BY clause.”

If you think about it, that sounds reasonable as the row_number requires an explicit order.

How’s that possible then that Linq2Sql will not throw an exception for such query? Well, it seems that if the ordering is missing from the query tree, Linq2Sql will …………… .

I am sure the curious reader will find the answer on his/her own.