Home ASP.NET MVC | datatables server side filtering not working
Reply: 1

ASP.NET MVC | datatables server side filtering not working

Sizzling Code
Sizzling Code Published in 2015-08-30 13:52:23Z

Im new to .NET, entity framework, delegates etc.

What i am trying to do is to use datatables in ASP.NET MVC5 application..

i am following this tutorial


its the part 1 actually. and datatables showing data fine if not filtered. but when i try to filter,i get error.

Below are screenshots of errors.

This is a demo table for learning purpose. It shows the categories in my database table..

I have this entity class for categories

    public class Category
        public virtual int CategoryID { get; set; }
        public virtual int ParentCategory { get; set; }
        public virtual string Name { get; set; }

        [Column(TypeName = "ntext")]
        public virtual string Description {get;set; }
        public virtual ICollection<Product> Products { get; set; }

Here is the controller i am using for serving ajax purpose of datatables.

public ActionResult CategoriesList_DT(jQueryDataTableParamModel param)

            var allCategories = _db.Categories;
            IEnumerable<Category> filteredCategories;

            if (!string.IsNullOrEmpty(param.sSearch))
                //Used if particulare columns are filtered 
                var nameFilter = Convert.ToString(Request["sSearch_1"]);
                var descriptionFilter = Convert.ToString(Request["sSearch_2"]);

                //Optionally check whether the columns are searchable at all 
                var isNameSearchable = Convert.ToBoolean(Request["bSearchable_1"]);
                var isDescriptionSearchable = Convert.ToBoolean(Request["bSearchable_2"]);

                filteredCategories = _db.Categories
                   .Where(c => isNameSearchable && c.Name.ToLower().Contains(param.sSearch.ToLower())
                               isDescriptionSearchable && c.Description.ToLower().Contains(param.sSearch.ToLower()));
                filteredCategories = allCategories;

            var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
            var isDescriptionSortable = Convert.ToBoolean(Request["bSortable_2"]);
            var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
            Func<Category, string> orderingFunction = (c => sortColumnIndex == 1 && isNameSortable ? c.Name :
                                                           sortColumnIndex == 2 && isDescriptionSortable ? c.Description :

            var sortDirection = Request["sSortDir_0"]; // asc or desc
            if (sortDirection == "asc")
                filteredCategories = filteredCategories.OrderBy(orderingFunction);
                filteredCategories = filteredCategories.OrderByDescending(orderingFunction);

            var displayedCategories = filteredCategories.Skip(param.iDisplayStart).Take(param.iDisplayLength);
            var result = from c in displayedCategories select new[] { Convert.ToString(c.CategoryID), c.Name, c.Description };
            return Json(new
                sEcho = param.sEcho,
                iTotalRecords = allCategories.Count(),
                iTotalDisplayRecords = filteredCategories.Count(),
                aaData = result

and this is the js script for jquery datatables.

@section Scripts {
    <script type="text/javascript">
        $(document).ready(function () {

            var oTable = $('#datatable').dataTable({
                "bServerSide": true,
                "sAjaxSource": "@Url.Action("CategoriesList_DT", "Categories", new { area = "Admin" })",
                "bProcessing": true,
                "aoColumns": [
                                    "sName": "ID",
                                    "bSearchable": false,
                                    "bSortable": false,
                                    "fnRender": function (oObj) {
                                        return '<a href=\"Company/Details/' + oObj.aData[0] + '\">View</a>';
                                { "sName": "Name" },
                                { "sName": "Description" }


I am not sure what error i am getting and why i am getting this error. surely i am new to this.. Also if possible plz tell me what should be the right way of implementing datatables if i am not doing it right.

Community Reply to 2017-05-23 12:29:43Z

Problem fixed.

It was problem with ToLower() not working with nText. i dnt knw why but searched net again, i guess we can use nvarchar(max) instead of nText it can also store big text data :)

Linq to Entities : using ToLower() on NText fields

Got little info from here too about nvarchar(max)

nvarchar(max) vs NText

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.326277 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO