Limit and Offset

Limit and Offset allows you to limit the number of results returned from the database, this method is highly correlated with the OrderBy and OrderByDesc methods.

var latestPosts = new Query("Posts").OrderByDesc("Date").Limit(10)

In Sql Server

SELECT TOP (10) * FROM [Posts] ORDER BY [Date] DESC

In PostgreSql

SELECT * FROM "Posts" ORDER BY "Date" DESC LIMIT 10

In MySql

SELECT * FROM `Posts` ORDER BY `Date` DESC LIMIT 10

Skipping records (Offset)

if you want to skip some records, use the Offset method.

var latestPosts = new Query("Posts").OrderByDesc("Date").Limit(10).Offset(5);

In Sql Server

SELECT * FROM [Posts] ORDER BY [Date] DESC OFFSET 5 ROWS FETCH NEXT 10 ROWS

In Legacy Sql Server (< 2012)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [row_num] FROM [Posts]) AS [subquery] WHERE [row_num] BETWEEN 6 AND 15

In PostgreSql

SELECT * FROM "Posts" ORDER BY "Date" DESC LIMIT 10 OFFSET 5

In MySql

SELECT * FROM `Posts` ORDER BY `Date` DESC LIMIT 10 OFFSET 5

Data pagination

You can use the ForPage method to easily paginate your data.

var posts = new Query("Posts").OrderByDesc("Date").ForPage(2);

By default this method will return 15 rows per page, you can override this value by passing an integer as a 2nd parameter.

Note: ForPage is 1-based so pass 1 for the first page

var posts = new Query("Posts").OrderByDesc("Date").ForPage(3, 50);

Skip & Take

If you are coming from a Linq background here is a bonus for you. You can use the Skip and Take methods as aliases for Offset and Limit enjoy :)