JQuery Datatables Server Side Processing and Filter

Question

I have a couple of datatables which contain a lot of data so i am using server side processing in order to provide the data in order to increase performance. Generally these work absolutely fine. However, the issue comes when trying to filter on the tables. It doesnt seem to respect the where clauses in my LINQ statement and i am at a loss as to why.

One of my datatable initisalisation examples looks like this:

$('#link-list').dataTable({
        'bServerSide': true,
        'sAjaxSource': '@Url.Action("LazyLoadComms", "Communication")',
        'bProcessing': true,
        async: false,
        'aoColumns': [
            {
                'mDataProp':'Id'
            },
            {
                'mDataProp': 'Customer'
            },
            {
                'mDataProp': 'Receiver'
            },
            {
                'mDataProp': 'PartNo'
            },
            {
                'mDataProp': 'DateOpened'
            }
        ],
        bAutoWidth: false,
        bLengthChange: false,
        pageLength: 10,
        'order': [[4, 'desc']]
    });

And the server side method is as follows:

public ActionResult LazyLoadComms(JqueryDataTableParams param)
    {
        var communications = _uow.CommunicationService.Get().ToList();

        IEnumerable<Communication> filteredComms;
        if (!string.IsNullOrEmpty(param.sSearch))
        {
            filteredComms = communications.Where(c => !string.IsNullOrEmpty(c.Customer.Name) ? c.Customer.Name.ToLower().Contains(param.sSearch.ToLower()) : false
                                                    || !string.IsNullOrEmpty(c.Receiver) ? c.Receiver.ToLower().Contains(param.sSearch.ToLower()) : false
                                                    || !string.IsNullOrEmpty(c.PartNo) ? c.PartNo.ToLower().Contains(param.sSearch.ToLower()) : false);
        }
        else
        {
            filteredComms = communications;
        }
        var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);

        Func<Communication, string> orderingFunction = (c => sortColumnIndex == 0 ? c.CommunicationId.ToString() :
                                                                sortColumnIndex == 1 ? c.Customer.Name :
                                                                sortColumnIndex == 2 ? c.Receiver :
                                                                sortColumnIndex == 3 ? c.PartNo :
                                                                c.DateOpened.ToLongDateString());

        var sortDirection = Request["sSortDir_0"];
        if (sortDirection == "asc")
            filteredComms = filteredComms.OrderBy(orderingFunction);
        else
            filteredComms = filteredComms.OrderByDescending(orderingFunction);

        var displayedComms = filteredComms
                                .Skip(param.iDisplayStart)
                                .Take(param.iDisplayLength)
                                .Select(c => new
                                {
                                    Id = c.CommunicationId,
                                    Customer = c.Customer.Name,
                                    Receiver = c.Receiver,
                                    PartNo = c.PartNo,
                                    DateOpened = c.DateOpened.ToShortDateString() + " " + c.DateOpened.ToShortTimeString()
                                });

        var json = Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = communications.Count(),
            iTotalDisplayRecords = filteredComms.Count(),
            aaData = displayedComms
        },
                            JsonRequestBehavior.AllowGet);

        return json;
    }

They do not seem very consistent. As in this example it never returns correct part numbers and is on and off as to whether it returns other columns that match the input also.

The input is always one word without spaces and is converted to lower case so it should match but does not return correctly.

Any help is greatly appreciated.

Many Thanks!!!!

Majid Basirati · Accepted Answer · 2016-09-25 07:08:21Z

2

Please replace below code. This might be issue of Speed. Please share if any error you got:

filteredComms = communications.Where(c => (!string.IsNullOrEmpty(c.Customer.Name) && c.Customer.Name.ToLower().Contains(param.sSearch.ToLower()))
    || (!string.IsNullOrEmpty(c.Receiver) && c.Receiver.ToLower().Contains(param.sSearch.ToLower()))
    || (!string.IsNullOrEmpty(c.PartNo) && c.PartNo.ToLower().Contains(param.sSearch.ToLower())));

edited Sep 25, 2016 at 7:08

Majid Basirati

2,9053 gold badges27 silver badges47 bronze badges

answered Sep 22, 2016 at 9:04

Nikunj Ratanpara

1481 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DaRoGa Over a year ago

But this code is what does the filtering and the filtering is the issue i need to fix. it will return data fine if i dont do any filtering but its when im trying to filter by the columns

DaRoGa Over a year ago

I can remove it and it returns me all records fine. but thats not the point. the point of it is that when im filtering against the columns in that piece of code, it is not taking all columns into account

Nikunj Ratanpara Over a year ago

You want records which have Search text regardless off column or Records which have records with all column containing that text?

markpsmith Over a year ago

Who said remove it? @NikunjRatanpara said REPLACE it.

Collectives™ on Stack Overflow

JQuery Datatables Server Side Processing and Filter

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related