Problem: I run into issue where pagination was bringing almost all the rows from the collection and then doing pagination. In other words it is doing in memory pagination which is inefficient. Recommendation: In order to do MongoDB pagination: avoid using LINQ Query.
Solution:
With following way you can easily and efficiently do filtering, sorting and pagination on mongoDB collection. Below code snippet will bring back only records which are required. Example: If pageSize is 10 it will only bring back 10 records. This code is tested on over 12 million records and it is working efficiently. I am open for any comment/suggestion to make it more robust.
Note: It uses predicate way of writing lambda expression.
C# code for filtering, sorting and pagination on MongoDB Collection
//Get MongoDB Collection
var collection = database.GetCollection
//Query the collection with criteria formed as lambda expression.
var filter = Builders
.Filter
.Where
(
a => a.City == "Ahmedabad" &&
a.DateOfBirth < DateTime.Now.AddYears(-18)
);
//Build sort definition based on your need
var sort = new SortDefinitionBuilder
//Apply Pagination and return back list of records.
var resultSet = collection
.Find(filter)
.Sort(sort)
.Skip(pageSize * (currPage - 1))
.Limit(pageSize).ToList();
