Stateless paging in SQL Server CE 3.51 using Entity Framework

If you are bound to Microsoft SQL Server Compact Edition 3.51 (or indeed, I think any of 3.x) using Entity Framework then you may have found the lack of support for server-stateless paging through queries:

SELECT VALUE employee 
FROM Employees
ORDER BY employee.Name 
SKIP(@skip) LIMIT (@limit)

Both the LINQ Skip() and Take() extensions, along with the Entity SQL commands SKIP and LIMIT commands all come back with runtime errors.

Paging with TOP

This is an alternative SQL keyword that can be employed in SQL Server 3.51 CE. Normally it provides a function similar to LIMIT, although has no equivalent to SKIP, which makes it pretty useless. However, with a bit of fudging, it's possible to come up with an Entity SQL query that does the same thing:

SELECT VALUE skip_query FROM
(
    SELECT VALUE TOP (@count) take_query 
    FROM 
    (
        SELECT VALUE TOP(@end) employee
        FROM Company.Employees AS employee
        ORDER BY employee.Name ASC, employee.Id ASC
    ) 
    AS take_query
    ORDER BY take_query.Name DESC, employee.Id DESC
) 
AS skip_query
ORDER BY skip_query.Name ASC, employee.Id ASC;

Considerations

Admittedly, we've got the parameters count and end to use, so it's not exactly the same as SKIP and LIMIT, but it works just as well.

If you're worried about efficiency, the improvements of properly paging should vastly overcome the use of nested statements and their performance impact on CE.

Also, if you look at the documentation for TOP it does give a warning that it's non-deterministic. However note that as long as an ORDER BY clause is specified, this is negated. Do however note that each record must have a unique placement in the ORDER BY clause. I highly recommend putting the primary key as a secondary ordered field to ensure deterministicity, as shown in the example above.

Source Code

There's more than one way to implement an Entity SQL Query, but if you want implicit object materialisation, then using an ObjectQuery instance is the easiest way.

private const string SkipLimitQuery = "SELECT VALUE skip_query...";
private Employee[] GetEmployeesPaged( int start, int count )
{
    using ( Company context = new Company() )
    {        
        ObjectQuery<DAL.Rom> query = context.CreateQuery<Employee>( SkipLimitQuery );
        command.Parameters.Add( new ObjectParameter( "count", count ) );
        command.Parameters.Add( new ObjectParameter( "end", start + count ) );

        return query.ToArray();
    }
}