What I m missing to implement case insensitive search?

Question

Here is my js file. I 've done everything in the server side. And implemented all basic and some advance feature of Datatable plugins. But the search function is case sensitive. If I search for "oil" it shows oil only but not OIL.

$(document).ready(function () {
var oTable = $('#myDataTable').dataTable({
    "bServerSide": true,
    "sPaginationType": "full_numbers",
    "sAjaxSource": "/DB/AjaxOil",
    "bProcessing": true,
    "Search": {
        "caseInsensitive": true
              },
    "aoColumns": [
        {
            "sName": "OilId",
            "aTargets": [0],    //Edit column
            "mData": "OilId",
            "bSearchable": false,
            "bSortable": false,
            "mRender": function (data, type, full) {
                var id = full[0]; //row id in the first column
                return "<a>"+id+"</a>";
            }
        },
        { "sName": "CommonName" },
        { "sName": "BotanicalName" },
        { "sName": "PlantParts" },
        { "sName": "Distillation" }
    ],
    "columnDefs": [
        {
            "targets": [0],
            className: "hide_column",
            "searchable": false
        }
    ]

});

});

And Here is my ajax function

          public ActionResult AjaxOil(JQueryDataTableParamModel param)
    {
        IEnumerable<Oil> allOils = _context.Oils.ToList();
        IEnumerable<Oil> filteredOils;
        if (!string.IsNullOrEmpty(param.sSearch))
        {
            filteredOils = allOils
                     .Where(c => c.CommonName.Contains(param.sSearch)
                                 ||
                                 c.BotanicalName.Contains(param.sSearch)
                                 ||
                                 c.PlantParts.Contains(param.sSearch)
                                 ||
                                 c.Distillation.Contains(param.sSearch));
        }
        else
        {
            filteredOils = allOils;
        }

        var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
        Func<Oil, string> orderingFunction = (c => sortColumnIndex == 1 ? c.CommonName :
                                                            sortColumnIndex == 2 ? c.BotanicalName :
                                                            c.PlantParts);


        var distillationFilter = Convert.ToString(Request["sSearch_4"]);
        var commonFilter = Convert.ToString(Request["sSearch_1"]);
        var botanicalFilter = Convert.ToString(Request["sSearch_2"]);
        var plantFilter = Convert.ToString(Request["sSearch_3"]);


        if (!string.IsNullOrEmpty(commonFilter))
        {
            filteredOils = filteredOils.Where(c => c.CommonName.Contains(commonFilter));

        }

        if (!string.IsNullOrEmpty(botanicalFilter))
        {
            filteredOils = filteredOils.Where(c => c.BotanicalName.Contains(botanicalFilter));

        }

        if (!string.IsNullOrEmpty(plantFilter))
        {
            filteredOils = filteredOils.Where(c => c.PlantParts.Contains(plantFilter));

        }

        if (!string.IsNullOrEmpty(distillationFilter))
        {
            filteredOils = filteredOils.Where(c => c.Distillation.Contains(distillationFilter));

        }


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

        var displayedOils = filteredOils
                           .Skip(param.iDisplayStart)
                           .Take(param.iDisplayLength);
        var result = from c in displayedOils
                     select new[] { Convert.ToString(c.OilId), c.CommonName, c.BotanicalName, c.PlantParts, c.Distillation };
        return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = allOils.Count(),
            iTotalDisplayRecords = filteredOils.Count(),
            aaData = result
        },
                         JsonRequestBehavior.AllowGet);
    }

P.s. The database has 5million row so please suggest with performance point of view too.

Answer 1

As Shyju mentioned, refer to this post: Case insensitive 'Contains(string)'

This will give you a wholistic idea of what to expect.

Here is a small excerpt from the post for your reference:

To test if the string paragraph contains the string word (thanks @QuarterMeister)

culture.CompareInfo.IndexOf(paragraph, word, CompareOptions.IgnoreCase) >= 0

Where culture is the instance of CultureInfo describing the language that the text is written in.

In addition, I encourage you to visit this article which has an exhaustive comparison of various methods’ performances while checking if a string occurs within a string. This should help you decide what approach to take for better performance.

http://cc.davelozinski.com/c-sharp/fastest-way-to-check-if-a-string-occurs-within-a-string

Based on your question, you may have to create an extension method which will use different approach to preform check based on the type of input to achieve best performance.

Hope this helps!

Answer 2

First of all you should not use _context.Oils.ToList(); then you will retrieve all your records from the database before filtering them. If you place the ToList() after .Take(param.iDisplayLength) all your selection code will be translated to a query in your database and only the relevant records will be retrieved. There is a difference between Contains executed by the Linq extension (case sensitve; see also this SO question) and the Contains that will we translated by the Entity Framework to a LIKE SQL statement (see this SO question and this video about tracing the SQL statements generated by your code).