Monday, March 27, 2017

Efficient way of performing filtering, sorting and pagination with MongoDB C#

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("Member");


//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()
                       .Descending(x => x.DateOfJoin);


//Apply Pagination and return back list of records.
var resultSet = collection
                     .Find(filter)
                     .Sort(sort)
                     .Skip(pageSize * (currPage - 1))
                     .Limit(pageSize).ToList();


No comments:

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape