Perform wildcard search of all (displayed) model fields in MVC?

Question

I have an MVC5 View where I am using the Grid.MVC component (https://gridmvc.codeplex.com/). This allows me to easily display data from my Oracle DB and has out of the box functionality for Sorting/Filtering each Data Column. What I am trying to implement now is a Wildcard Search across all fields in my grid. For example, if I search the number "2" I'd like to return all records that contain a "2" be they string, decimal, or DateTime.

The filter capability on this grid performs filtering (for individual columns) partially by modifying the URL (http://homeURL/?grid-filter=Location.DEPT__1__accounting) such as 1 being Equals, 2 being Contains, 3 being StartsWith, and 4 being EndsWith and then after the next 2 underscores being the search criteria.

I first thought I was going down the right path by using JavaScript to modify to the desired URL via daisy-chaining all fields with the search criteria using a CONTAINS. I then noticed that decimal fields like [Cost] and DateTime (Oracle DB) fields like [Acquired_Date] have criteria settings of Equals, Greater Than, and Less Than, so I tried:

$('#SearchIcon').on("click", function (e) {
            window.location = window.location.href.substr(0, window.location.href.indexOf('?'));
            window.location = "?grid-filter=FIELD1__2__" + document.getElementById("Search").value +
                              "&grid-filter=FIELD2__2__" + document.getElementById("Search").value +
                              "&grid-filter=COST__1__" + document.getElementById("Search").value +
                              // etc. -- ALL FIELDS
                              "&grid-filter=NOTE__2__" + document.getElementById("Search").value;
        });

This technically functions, but with the [&] is searching for a record(s) that have the corresponding search criteria in EVERY field. What I need is something similar, but with an OR [||] conditioning ---- unfortunately the grid component does not contain this form of functionality.

I then thought to pass the search criteria to a controller action and use it via a multi-WHERE clause and return only the records fitting the filter to my View:

public ActionResult SearchAssets(string searchCriteria)
        { 
            fillPagingIntervalList();
            var assetSearchResults = db.ENTITY_COLLECTION.Where(m => m.ID.ToString() == searchCriteria ||
            m.Model.ToString() == searchCriteria ||
            m.COST.ToString() == searchCriteria ||
            // etc. -- ALL FIELDS
            ).FirstOrDefault();
            var assetCount = db.ENTITY_COLLECTION.ToList().Count();
            return View(assetSearchResults);
        }

This resulted in an error with the WHERE cluase, stating to view the Inner Exception for details -- ORA-12704: character set mismatch MVC. I then reduced my multiple conditions down to just 2 fields to be searched for debugging:

var assetSearchResults = db.ENTITY_COLLECTION.Where(m => m.ID.ToString() == searchCriteria ||
            m.Model.ToString() == searchCriteria).FirstOrDefault();

Resulting in: EntityCommandExecutionException was unhandled by user code.

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.dll but was not handled in user code

Additional information: An error occurred while executing the command definition. See the inner exception for details.

Inner Exception: ORA-00932: inconsistent datatypes: expected - got NCLOB

Anyone have an idea on how to get what I want working? I also tried .Where(...con1...).Where(...con2...).Where(...etc...) with the same error resulting. I figured a wildcard search across all fields would be difficult to implement, but this is proving to be a whole bigger animal than I anticipated.

Answer 1

This will be very slow, but try this, which will load the entire collection into objects and let LINQ do the filtering on the client side:

public ActionResult SearchAssets(string searchCriteria)
    { 
        fillPagingIntervalList();
        var assetSearchResults = db.ENTITY_COLLECTION.ToList().Where(m => m.ID.ToString() == searchCriteria ||
        m.Model.ToString() == searchCriteria ||
        m.COST.ToString() == searchCriteria ||
        // etc. -- ALL FIELDS
        ).FirstOrDefault();
        var assetCount = db.ENTITY_COLLECTION.ToList().Count();
        return View(assetSearchResults);
    }

You could try something like this:

public ActionResult SearchAssets(string searchCriteria)
    { 
        fillPagingIntervalList();
        var assetSearchResults = db.ENTITY_COLLECTION.Where(m => m.ID.ToString() == searchCriteria)
        .Union(db.ENTITY_COLLECTION.Where(m =>m.Model.ToString()==searchCriteria))
        .Union(db.ENTITY_COLLECTION.Where(m =>m.COST.ToString() == searchCriteria))
        // etc. -- ALL FIELDS
        var assetCount = db.ENTITY_COLLECTION.ToList().Count();
        return View(assetSearchResults);
    }

Although, ultimately I would suggest looking into something like a predicate builder. Seems to be what you are doing anyhow.