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

1.3k views Asked by At

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.

1

There are 1 answers

8
Robert McKee On BEST ANSWER

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.