Support for translate LINQ Skip method to SQL

[Burgyn]

Breaking Changes

• Arguments of QueryProvider's constructors has changed. Argument of type ISqlExpressionVisitor is now ISqlExpressionVisitorFactory. Default implementations are SqlServerSqlExpressionVisitorFactory and MsAccessSqlExpressionVisitorFactory.

Description

Linq method Take is actualy translated into SQL. But method Skip isn't. Please support Skip method.

Common scenario:

using(var database = new Database("connection string ...", SqlServerDataHelper.ClientId)
{
  var people = database.Query<Person>().OrderBy(p=> p.Id).Skip(10).Take(10)
}

to be translated into:

SELECT Id, FirstName, ... FROM Person ORDER BY Id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

This syntax is available only in Ms SQL, so exception on MsAccess should be thrown.

Notes

• This syntax is supported from SQL Server 2012. We need to support older versions too.
• We need to support Microsoft Access.
• Proposed solution is, when OFFSET/LIMIT is not supported out of the box, we will do it manually over returned data (IEnumerable<T>).
• OFFSET is allowed only when ORDER BY is specified. If there is no ORDER BY, InvalidOperationException will be thrown.

[satano]

I am not sure if it can be implemented in LINQ, but why to throw exception on MS Access? We can enumerate through result set and skip rows manually.