Dynamic High Performance Query Builder for MongoDB using DataTables for Over a Million Records

• Download source - 18.8 MB
• Live demo

Introduction

There was a need plausible data grid, capable of performing specific and multiple column searches, sorting, dynamic page sizing, using MongoDB, it handles very large data (over a million records), had elegant code structure, least coding effort and most of all performance. The intention is to amalgamate these features in a single, easy to code and easy to manage solution without degrading the performance aspect.

Pre-Requisites

In order to follow this article, you need to have some understanding of MVC Framework. If you think you have sufficient expertise, then you are good to read further through this article.

Abstraction

The solution is a blend of DataTables and MongoDB driver, DataTables is used on client side, it handles display grid data and provider searching and ordering UI feature and sends user search query to the server. At server end, MongoDB driver is used to build up flexible and high performance query. For further explanation, the code is broken up into two, client side and server side code.

Client Side Code

At client side, DataTables is king, here we are using its server-side processing feature. Before going further, DataTables is a plug-in for the jQuery JavaScript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table. It handles and performs our demanded functionality from our client side. Most importantly, it invokes Ajax request to the server for each draw of the information on the grid or page (i.e., when paging, ordering, searching, etc.). It sends a number of variables to the server (its DataTables build in feature) to allow server side to process this Ajax request and return's JSON data. You can download DataTables plugin from here or just add up the coming line to your page.

<link href="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.css" 
 rel="stylesheet" type="text/css" />
<script type="text/javascript" 
 src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>

DataTables Table Structure

Here is an example of table structure for DataTables plugin to use, you can see that we have define two header for the table, first header contains the column name while the next header is a place holder for our search controls.

<table id="grid" class="table table-striped small stripe hover cell-border 
 compact order-column row-border" cellspacing="0" style="font-size: small;">
                    <thead>
                        <tr>

                            <th width="20%">Company</th>
                            <th width="15%">Contact Person</th>
                            <th width="7%">Phone</th>
                            <th width="7%">Fax</th>
                            <th width="10%">City</th>
                            <th width="10%">Region</th>
                            <th width="5%">Status</th>
                            <th width="14%">Date</th>
                            <th width="12%">Balance</th>
                        </tr>
                        <tr id="filterrow" class="hidden-xs hidden-sm">
                            <th width="20%" style="padding:0px 0px;border:0px;"></th>
                            <th width="15%" style="padding: 0px 0px; border: 0px;"></th>
                            <th width="7%" style="padding: 0px 0px; border: 0px;"></th>
                            <th width="7%" style="padding: 0px 0px; border: 0px;"></th>
                            <th width="10%" style="padding: 0px 0px; border: 0px;"></th>
                            <th width="10%" style="padding: 0px 0px; border: 0px;"></th>
                            <th width="5%" style="padding: 0px 0px; border: 0px;"></th>
                            <th width="14%" style="padding: 0px 0px; border: 0px;"></th>
                            <th width="14%" style="padding: 0px 0px; border: 0px;
                             text-align:center"></th>
                        </tr>
                    </thead>
                </table>

DataTables Table's Initialization

Before going into the initialization step, let me explain the variable _columnFilterMannifest, it holds manifest and data for all the column search controls which will reside on the second header row as discussed previously. This variable is set during our default action method call. On page ready function, call setGridFilter('grid',_columnFilterManifest) function is invoked, this function is present in our Grid.js to encapsulates functionalities, to make it short, the first parameter is the id of our structure table and the second is the manifest which we had discussed earlier, not long ago. Now our created table structure is initialize from DataTables plugin, you can go through its manual here.

At the end, initGridFilter() method is called which is again in Grid.js, and encapsulated. Its core purpose is to assign trigger search feature to all the grid's columns search controls.

var _columnFilterManifest =  JSON.parse( @Html.Raw(Json.Encode
                             ( ViewData["columnFilterManifest"].ToString()  ) ) );

    $(document).ready(function () {
        setGridFilters('grid',_columnFilterManifest);

        var table = $('#grid').DataTable({
            "order": [[1, "asc"]],
            "orderCellsTop": true,
            "pagingType": "full_numbers",
            "scrollCollapse": true,
            rowReorder: {
                selector: 'td:nth-child(2)'
            },
            responsive: true,
            "processing": true,
            "serverSide": true,
            "lengthMenu": [[10,15,25, 50], [10,15,25, 50]],
            "ajax": "/Customer/GetPage",
            "columns": [
           { "data": "company" },
           { "data": "contactPerson" },
           { "data": "phone" },
           { "data": "fax" },
           { "data": "city" },
           { "data": "region" },
           { "data": "status" },
           { "data": "createdDate" },
           { "data": "balance" },
            ],
            "columnDefs": [
                   {
                       "render": function (data, type, row) {
                           return accounting.formatMoney(data);
                       },
                       "targets": 8
                   },

                    {
                    "render": function (data, type, row) {
                    return moment(data).format("MM/DD/YYYY");
                        },
                        "targets": 7
                    },

                    { "className": "dt-right", "targets": 8 },
            ],
     
            "autoWidth": false,
        });

        initGridFilter(table, 'grid');                
    });

Server Side

On server side, we have MongoDB driver to make our aspiration possible. One of its controller action method set _columnFilterManifest variable for the client side which is used to initiate and populate the column search controls which we had discussed earlier in our client side code discussion.

/// <summary>
/// Default page controller method
/// </summary>
/// <returns></returns>
public ActionResult Index()
{
    BusinessLogic.Entity.Customer objCust = new BusinessLogic.Entity.Customer();
    ViewData["columnFilterManifest"] = 
          new JavaScriptSerializer().Serialize(objCust.GetColumnFilterManifest());
    return View("list");
}

DataTables shall call GetPage() controller Action method to get paginated data here. You can see it calling Business Logic layer entity to get entity array result which sends back to the client in JSON format. Its return JSON object contains properties such as data (which contains entity array), recordsTotal (entity array size), recordsFiltered (entity array size) and draw (its used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTable).

/// <summary>
/// Provides grid paginated records as JSON
/// </summary>
/// <returns>DataTables paginated data</returns>
public JsonResult GetPage()
{
    int draw = Convert.ToInt32(Request["draw"]);
            BusinessLogic.Entity.Customer objCust = new BusinessLogic.Entity.Customer();
            CustomerGrid[] customers = objCust.Search(this);
    return Json(new
            {
                data = customers,
                recordsTotal = base.GetGridTotalRows(),
                recordsFiltered = base.GetGridTotalRows(),
                draw = draw
            },
            "application/json", Encoding.UTF8, JsonRequestBehavior.AllowGet);
}

Now moving on to the entity business logic layer which got invoked from controller action method, which we have just discussed that is Search(IBaseController iBaseController), it performs all of our pagination activities in this Business Logic layer entity method that filter by clause and order by clause shall be added to the query dynamically according to DataTables query.

#region Document Search
/// <summary>
/// Get list of filtered customers
/// </summary>
/// <param name="iBaseController">Interface of invoke class</param>
/// <returns>Customer array</returns>
public virtual CustomerGrid[] Search(IBaseController iBaseController)
{
    GridParameterCollector gridParamCollet =
                           iBaseController.GenerateGridParameterCollector();
    IMongoCollection<CustomerGrid> collection =
                     database.GetCollection<CustomerGrid>("Customer");
    List<FilterDefinition<CustomerGrid>> filterDef =
               new List<FilterDefinition<CustomerGrid>>(); ;

    genericSearchText = gridParamCollet.searchText.Trim();      // set generic
                                                                // search value


    //Iterate through filter grid column to consturct query predicate
    foreach (ColumnParameterCollector column in gridParamCollet.columnCollector)
    {
        searchColumnText = column.columnSearchText.Trim();      // set current
                                                                // column search value
        switch (column.dataName)
        {

            case "company":
                string companyColumnText = searchColumnText;
                string companyGenericText = genericSearchText;

                EvaluateFilterMongoDBExpression(column,
                   Builders<CustomerGrid>.Filter.Regex(u => u.company,
                   new BsonRegularExpression(companyColumnText + "*", "i")),
                   Builders<CustomerGrid>.Filter.Regex(u => u.company,
                   new BsonRegularExpression(companyGenericText + "*", "i")),
                   x => x.company
                   );
                break;

            case "contactPerson":
                string contactPersonColumnText = searchColumnText;
                string contactPersonGenericText = genericSearchText;

                EvaluateFilterMongoDBExpression(column,
                   Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
                   new BsonRegularExpression(contactPersonColumnText + "*", "i")),
                   Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
                   new BsonRegularExpression(contactPersonGenericText + "*", "i")),
                   x => x.contactPerson
                   );
                break;

            case "phone":
                string phoneColumnText = searchColumnText;
                string phoneGenericText = genericSearchText;

                EvaluateFilterMongoDBExpression(column,
                   Builders<CustomerGrid>.Filter.Regex(u => u.phone,
                   new BsonRegularExpression(phoneColumnText + "*", "i")),
                   Builders<CustomerGrid>.Filter.Regex(u => u.phone,
                   new BsonRegularExpression(phoneGenericText + "*", "i")),
                   x => x.phone
                   );
                break;

            case "fax":
                string faxColumnText = searchColumnText;
                string faxGenericText = genericSearchText;

                EvaluateFilterMongoDBExpression(column,
                   Builders<CustomerGrid>.Filter.Regex(u => u.fax,
                   new BsonRegularExpression(faxColumnText + "*", "i")),
                   Builders<CustomerGrid>.Filter.Regex(u => u.fax,
                   new BsonRegularExpression(faxGenericText + "*", "i")),
                   x => x.fax
                   );
                break;

            case "city":
                string cityColumnText = searchColumnText;
                string cityGenericText = genericSearchText;

                EvaluateFilterMongoDBExpression(column,
                   Builders<CustomerGrid>.Filter.Regex(u => u.city,
                   new BsonRegularExpression(cityColumnText + "*", "i")),
                   Builders<CustomerGrid>.Filter.Regex(u => u.city,
                   new BsonRegularExpression(cityGenericText + "*", "i")),
                   x => x.city
                   );
                break;

            case "region":
                string regionColumnText = searchColumnText;
                string regionGenericText = genericSearchText;

                EvaluateFilterMongoDBExpression(column,
                   Builders<CustomerGrid>.Filter.Regex(u => u.region,
                   new BsonRegularExpression(regionColumnText + "*", "i")),
                   Builders<CustomerGrid>.Filter.Regex(u => u.region,
                   new BsonRegularExpression(regionGenericText + "*", "i")),
                   x => x.region
                   );
                break;

            case "status":
                string statusColumnText = searchColumnText;
                string statusGenericText = genericSearchText;

                System.Linq.Expressions.Expression<Func<CustomerGrid, bool>>
                            jobStatusFilterExpression = null;

                if (searchColumnText.Length != 0 && searchColumnText != "-1")
                {
                    jobStatusFilterExpression = x => x.status == statusColumnText;
                }

                EvaluateFilterMongoDB(column,
                   jobStatusFilterExpression,
                   x => x.status.Equals(statusGenericText),
                   x => x.status
                   );
                break;

            case "createdDate":
                if (searchColumnText.Trim().Length == 0)
                {
                    if (column.isColumnOrderable)
                    {
                        EvaluateFilterMongoDB(column,
                        null,
                        null,
                        x => x.createdDate
                        );
                    }

                    continue;
                }
                string[] firstSplit = searchColumnText.Split(',');

                if (firstSplit[0] == "false")
                {
                    continue;
                }

                string creationDateRangeColumnText = firstSplit[1];// searchColumnText;
                string[] creationDateRangeSplit =
                         creationDateRangeColumnText.Split('-');
                DateTime startDate = DateTime.Parse(creationDateRangeSplit[0]);
                DateTime endDate = DateTime.Parse(creationDateRangeSplit[1]);

                EvaluateFilterMongoDB(column,
                    x => x.createdDate >= startDate && x.createdDate <= endDate,
                    x => x.createdDate >= startDate && x.createdDate <= endDate,
                    x => x.createdDate
                    );
                break;

            case "balance":
                string balanceColumnText = searchColumnText;

                if (searchColumnText.Trim().Length == 0)
                {
                    if (column.isColumnOrderable)
                    {
                        EvaluateFilterMongoDB(column,
                        null,
                        null,
                        x => x.balance
                        );
                    }

                    continue;
                }

                string[] str = searchColumnText.Split(',');
                string strAmount = str[1].Trim();

                if (strAmount.Trim().Length == 0)
                {
                    if (column.isColumnOrderable)
                    {
                        EvaluateFilterMongoDB(column,
                        null,
                        null,
                        x => x.balance
                        );
                    }

                    continue;
                }

                double amount = double.Parse(strAmount);


                EvaluateNumericComparisonFilterMongoDB(column,
                           balanceColumnText,
                           "balance",
                           x => x.balance,
                           Builders<CustomerGrid>.Filter.Lte(c => c.balance, amount),
                           Builders<CustomerGrid>.Filter.Eq(c => c.balance, amount),
                           Builders<CustomerGrid>.Filter.Gte(c => c.balance, amount)
                );

                break;
        }
    }

    FilterDefinition<CustomerGrid> filter = null;  // filter variable

    // If andPreciate is enable
    if (isAndPredicateAdded)
    {
        filter = Builders<CustomerGrid>.Filter.And(andPredicateMongoDB); // andPredicate
    }

    // If orPreciate is enable
    if (isOrPredicateAdded)
    {
        filter = Builders<CustomerGrid>.Filter.Or(orPredicateMongoDB);
    }

    // Set total numbers or rows coming under the query
    if (iBaseController != null)
    {
        int count = 0;

        if (filter == null)
        {
            count = Convert.ToInt32(collection.Count(x => x.company != ""));
        }
        else
        {
            count = Convert.ToInt32(collection.Find(filter).Count());
        }
        iBaseController.SetGridTotalRows(count);
    }

    ProjectionDefinition<CustomerGrid> proj = Builders<CustomerGrid>.Projection
                                            .Include("Id")
                                            .Include("company")
                                            .Include("contactPerson")
                                            .Include("createdDate")
                                            .Include("phone")
                                            .Include("CreatedBy")
                                            .Include("fax")
                                            .Include("email")
                                            .Include("city")
                                            .Include("region")
                                            .Include("balance")
                                            .Include("status")
                                            ;

    // Slice required rows from pagination
    if (gridParamCollet != null)
    {
        if (filter == null)
        {
            customers = collection.Find(x => x.company != "")
                .Sort(sortDefMongoDB)
                .Project<CustomerGrid>(proj)
                .Skip(gridParamCollet.start).Limit(gridParamCollet.length).ToList();
        }
        else
        {
            customers = collection.Find(filter).Sort
            (sortDefMongoDB).Project<CustomerGrid>(proj).Skip
            (gridParamCollet.start).Limit(gridParamCollet.length).ToList();
        }
    }

    //Generating data
    return customers.ToArray();
}

In the first part of the above entity search method, GenerateGridParameterCollector() method is called which makes DataTables Ajax call querystring into desire object, in this case, it's turned into GridParameterCollector object definition of which is as given below. I belief that only comments shall do the job here.

/// <summary>
/// Summarize querystring object
/// </summary>
public class GridParameterCollector
{
   public int start{ get; set; }                 // Start row index
   public int length{ get; set; }                // Page size
   public string searchText  { get; set; }       // Generic search value
   public bool isSearchRegex { get; set; }       // Generic search expression value

   public List<ColumnParameterCollector> columnCollector { get; set; }
}

/// <summary>
/// Column level querystring object
/// </summary>
public class ColumnParameterCollector
{
    public int columnIndex  { get; set; }         // Column index
    public string orderDirection { get; set; }    // Order direction asc or desc
    public string dataName   { get; set; }        // Column name
    public string columnName    { get; set; }     // Column name
    public bool isColumnSearchable { get; set; }  // Flag for column is searchable or not
    public bool isColumnOrderable { get; set; }   // Flag for column is orderable or not
    public string columnSearchText { get; set; }  // Column search control value
    public bool isColumnSearchRegex { get; set; } // Column search expression control value
}

The next step is to build filter definition for final query. In subsequent step, gridParamCollect objects iteratable property columnCollector is iterated to append where clause to the query. Now in the next step, we shall discuss the search column filter evaluation method that would build up the predicate, functionality of which is encapsulated in business logic layer entity base class.

case "contactPerson":
                        string contactPersonColumnText = searchColumnText;
                        string contactPersonGenericText = genericSearchText;

                        EvaluateFilterMongoDBExpression(column,
                           Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson, 
                           new BsonRegularExpression(contactPersonColumnText + "*", "i")),
                           Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson, 
                           new BsonRegularExpression(contactPersonGenericText + "*", "i")),
                           x => x.contactPerson
                           );
                        break;

When dealing with column text search or select option (sometime), then EvaluteFilter method is called which has its definition in base class. It takes four parameters, first parameter is current iteration ColumnParameterCollector object which provides info regarding current iterated column, second one is lamda expression for column search only, for instance lamda expression x => x.email.StartWtih("codeproject") shall generate where clause equivalent to '%codeproject'. The third parameter is for generic search, it consumes value from the upper right search textbox of DataTables grid. The fourth parameter is lamda expression as well but with different purpose, it defines order by clause for the query. e.g., x => x.email, then query will be embedded with order by email clause.

case "status":
                       string statusColumnText = searchColumnText;
                       string statusGenericText = genericSearchText;

                       System.Linq.Expressions.Expression<Func<CustomerGrid, bool>>
                              jobStatusFilterExpression = null;

                       if (searchColumnText.Length != 0 && searchColumnText != "-1")
                       {
                           jobStatusFilterExpression = x => x.status == statusColumnText;
                       }

                       EvaluateFilterMongoDB(column,
                          jobStatusFilterExpression,
                          x => x.status.Equals(statusGenericText),
                          x => x.status
                          );
                       break;

For select option, column search approach is slightly different, only from second parameter in which lamda expression is dynamic defined in if condition. Rest is exactly the same as the last explanation.

case "balance":
                        string balanceColumnText = searchColumnText;

                        if (searchColumnText.Trim().Length == 0)
                        {
                            if (column.isColumnOrderable)
                            {
                                EvaluateFilterMongoDB(column,
                                null,
                                null,
                                x => x.balance
                                );
                            }

                            continue;
                        }

                        string[] str = searchColumnText.Split(',');
                        string strAmount = str[1].Trim();

                        if (strAmount.Trim().Length == 0)
                        {
                            if (column.isColumnOrderable)
                            {
                                EvaluateFilterMongoDB(column,
                                null,
                                null,
                                x => x.balance
                                );
                            }

                            continue;
                        }

                        double amount = double.Parse(strAmount);


                        EvaluateNumericComparisonFilterMongoDB(column,
                                   balanceColumnText,
                                   "balance",
                                   x => x.balance,
                                   Builders<CustomerGrid>.Filter.Lte(c => c.balance, amount),
                                   Builders<CustomerGrid>.Filter.Eq(c => c.balance, amount),
                                   Builders<CustomerGrid>.Filter.Gte(c => c.balance, amount)

                        );

                        break;

Numerical comparison is different from the previous one as dynamic binary tree expression is involved here, this functionality is performed by EvaluateNumericComparisionFilter() method, its second parameter is column search value, third column is concerned with entity object field name, and last one is lambda expression for order by clause.

History

• 4^th February, 2018: Initial version