asp.net-mvc,oracle,linq,entity-framework,asp.net-mvc-5 , Perform wildcard search of all (displayed) model fields in MVC?


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

Question:

Tag: asp.net-mvc,oracle,linq,entity-framework,asp.net-mvc-5

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:

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.


Related:


How to join 2 tables with select and count in single query


sql,oracle,left-join
I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the sql. Person: ID NAME AGE...

Best approach to upgrade MVC3 web app to MVC5?


c#,.net,asp.net-mvc,asp.net-mvc-5
I have a web application which runs on Azure which is currently running MVC3/C#, EF6.1, .NET4.5. I would like to upgrade it to MVC5 to be: a) Current b) Get benefit of new features c) Get Performance gains. This is a part of a performance project, so hopefully there will...

Angularjs resource with scope parameter


javascript,asp.net-mvc,angularjs,single-page-application
My problem is the following: I am trying to call resource with the following parameter and I get the following error: [$resource:badcfg] I tried fixing this in the past 3 hours and I cant seem to make it work. So, if i call it like this: $scope.komintent = Fakturi.komintenti.get({ id:...

HTTP Error 403.14


asp.net-mvc,visual-studio-2013
I'm developing my MVC 5 app using VS 2013, it was working fine, but suddenly it's giving the following error. HTTP Error 403.14 - Forbidden The Web server is configured to not list the contents of this directory. I've already read this post, and it didn't solve the problem. I've...

Adding where clause to IEnumerable Select


c#,asp.net-mvc,linq
I have the following line of code in my controller that selects the id's of the interests shown within a view (they are listed down the page). IEnumerable<int> interestIds = viewModel.ExistingInterests.Select(x => x.InterestId); However I want to be able to add a where clause to the select section. In ExisitingInterests...

Why is my View not displaying value of ViewBag?


c#,asp.net,asp.net-mvc,asp.net-mvc-4,razor
I have a little blog application with posts and tags. This is my model for Post: namespace HelloWorld.Models { public class Post { [Required] [DataType(DataType.Text)] public string Title { get; set; } [Required] [DataType(DataType.MultilineText)] public string Description { get; set; } [Required] [DataType(DataType.DateTime)] public DateTime PostDate { get; set; }...

Checkbox to be checked on having value Y


asp.net-mvc,knockout.js
I have a checkbox bound to a viewmodel's observable property, whose value will be Y or N. I want the the checkbox to be checked when the value is Y and unchecked when the value is N. How to achieve this? I am able to achieve it if the value...

SQL*Loader Control File Custom Date Format


oracle,toad,sql-loader
I need to import from a CSV file in which timestamps are given in the following format 2014-06-14T09:38:29 I tried the following in the control file for SQL*Loader but it doesn't work TIME DATE "YYYY-MM-DDTHH:MI:SS" and TIME DATE "YYYY-MM-DDTHH24:MI:SS" How can I parse this custom date? The error I get...

MVC 5 OWIN login with claims and AntiforgeryToken. Do I miss a ClaimsIdentity provider?


asp.net-mvc,asp.net-mvc-4,razor,asp.net-mvc-5,claims-based-identity
I'm trying to learn Claims for MVC 5 OWIN login. I try'ed to keep it as simple as possible. I started with the MVC template and inserted my claims code (see below). I get an error when I use the @Html.AntiForgeryToken() helper in the View. Error: A claim of type...

How to map between two entities before paging


c#,asp.net-mvc,entity-framework
Consider the code below: public ActionResult Index(int? page) { List<ProviderViewModel> viewModel = new List<ProviderViewModel>(); List<Provider> businessModel = db.Providers .OrderBy(t => t.Name); foreach (Provider provider in businessModel) { viewModel.Add(new ProviderViewModel(provider)); } int pageSize = 9; int pageNumber = (page ?? 1); return View(viewModel.ToPagedList(pageNumber, pageSize)); } I'm using PagedList.MVC (from NuGet) and...

Creating a viewmodel on an existing project


c#,asp.net,asp.net-mvc
I am trying to add a viewmodel to a project because I want my view to use two separate models. I've looked at different tutorials trying to learn how to do this but I am having some trouble. Before, the view was strongly binded(typed?) to the Person model, but now...

How to add validators for @Html.TextBox() without model


asp.net-mvc,asp.net-mvc-4
This is a part of my view @model bhavin.Models.Employee @using (Html.BeginForm("BuynowForm", "Home")) { <div class="form-group"> <label>Billing Address</label> @Html.TextBox("bill_address", null, new { @class = "form-control valid" }) </div> <p> <input type="submit" value="Submit" class="btn btn-primary" /> </p> } I want to add required validation to it. The billing_address textbox is not a...

Notice: Array to string conversion in “path of php file” on line 64


php,mysql,arrays,oracle
PHP CODE -: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $rows =0; $fp = fopen("leave1.csv","r"); if($fp){ while(!feof($fp)){ $content = fgets($fp); if($content) $rows++; } } fclose($fp); //echo $rows; $_SESSION['rows'] = $rows; ?>...

ASP.NET MVC posting list from view to controller


c#,.net,asp.net-mvc,razor
I have a view model in my ASP.NET MVC application: public class FiltersViewModel { public IEnumerable<SelectListItem> AvailableFilters { get; set; } // fills a drop down menu public IList<TechnologyFilter> TechnologyFilters { get; set; } public IList<ContractTypeFilter> ContractTypeFilters { get; set; } public FiltersViewModel() { this.TechnologyFilters = new List<TechnologyFilter>(); this.ContractTypeFilters =...

When adding a user to a role in asp.net mvc 4.5, i'm getting an error- “user (user name) not found”


c#,asp.net-mvc
Here is my account login controller. (My "auth" class method returns "user" or "admin" and is logged in accordingly). [HttpPost] public ActionResult Login(string userName, string pass) { Auth auth = new Auth(); if (auth.MyAuth(userName) == "user") { FormsAuthentication.SetAuthCookie(userName, true); return RedirectToAction("Index", "Home"); } else if(auth.MyAuth(userName) == "admin") { FormsAuthentication.SetAuthCookie(userName, true);...

How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?


oracle,plsql
I would like to catch specific exception for exception "ORA-06535: statement string in OPEN is NULL or 0 length" But couldn't figure out the exact "exception name" for it. Please note I don't want to catch it under generic other exceptions block.. EXCEPTION WHEN <exception_name1> THEN executable_statements; WHEN <exception_nameN> THEN...

How to get started with Visual studio 2012


c#,asp.net-mvc,asp.net-mvc-3,asp.net-mvc-4,visual-studio-2012
I want to create web application using Visual Studio 2012. The application should login into a website and report numerical findings on the UI. The language used will be Visual C#. I have loaded the application and selected on the menu: File> New > Projects. A window appears with multiple...

check if file is image


c#,asp.net,asp.net-mvc
I want to check if file is image. and then you will see a link where you can see the image. But the link only has to appear if file is link. I try it like this: if (!String.IsNullOrEmpty(item.FileName)) { var file = item.FileName; string[] formats = new string[] {...

Dynamically adding controls in MVC4


asp.net-mvc,asp.net-mvc-4
I am currently working on creating an MVC4 application where I want controls to be generated automatically from the database rows. I have the table in my database containing the questions and the control type in which it should be answered by the user. I am just thinking of a...

oracle sql error Case When Then Else


sql,oracle,oracle11g
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

ASP.NET MVC Client side time convert from model


javascript,asp.net-mvc,model
My cshtml page contains the following code for DateTime, which is some server time fetched from sql. <tbody> @{ foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.someDate) </td> The type of the someDate property is DateTime. I would like to show the time in respect to client/browser...

Show/hide tinymce with radio buttons


c#,asp.net,asp.net-mvc,tinymce
I try to show/hide a tinymce with radobutton. Like yes/no. So there are two radio buttons. yes - will show the tiny mce and no will hide the tinymce. I have this: showing tiny mce: <div class="form-group"> @Html.Label(Resources.Entity.Product.PdfMessage, new { @class = "text-bold control-label col-md-2" }) <div class="col-lg-6 col-md-8 col-sm-10...

Oracle SQL - Returning the count from a delimited field


oracle
I'm fairly inexperienced with SQL so hopefully this question is not too silly. Here is the scenario: I have a VARCHAR2 column that stores a series of values delimited by product. Depending on on the account, they can have one or multiple products. I'm trying to write a query that...

Knockout JS Validation not working


javascript,asp.net-mvc,knockout.js
I am a newbie in Knockout JS. i want to apply validations in KO. i have used plugin knockout.validation.min.js . I have implemented it like this but not working My View Model $(document).ready(function myfunction() { ko.applyBindings(new EmployeeKoViewModel()); }) var EmployeeKoViewModel = function () { var self = this; self.EmpId =...

How to use Autofac to inject specific implementation in constructor


c#,asp.net-mvc,dependency-injection,autofac
I have two classes that take a ILastActivityUpdator as a constructor parameter: UserService and AnonymousUserService. public AnonymousUserService(ILastActivityUpdator lastActivityUpdator) { if (lastActivityUpdator == null) { throw new ArgumentNullException("lastActivityUpdator"); } this.lastActivityUpdator = lastActivityUpdator; } And similar as above for UserService: public UserService(ILastActivityUpdator lastActivityUpdator) { if (lastActivityUpdator == null) { throw new ArgumentNullException("lastActivityUpdator");...

like and regexp_like


sql,regex,oracle,oracle11g,regexp-like
For like we have %. for ex: if we give ad% it ll fetch all the records which starts with ad but i should use regexp_like. for regexp_like what can be used so that it acts as % for like. i cant use ^ad because from UI we ll give...

Exclude / ignore weekends in Oracle SQL


sql,oracle
I made a query that groups text together and their start and end date, i want to exclude/ignore the weekends. In this example 11-12 and 18-19 april are the weekends create table t ( d date, v varchar2(10)); insert into t values (date '2015-04-10', 'ne'); insert into t values (date...

update a table from another table using oracle db


sql,oracle
I have to update one table from another one: I can do the update with the MySQL sgbd: update product pr , provider p set pr.provider_name = p.name where p.provider_id = pr.provider_id ; but when I try to do it with oracle : I tried this query for oracle UPDATE...

SQL Developer does not connect with SID as defined in tnsnames.ora


oracle,oracle-sqldeveloper
My tnsnames.ora looks like this: ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)...

Avoid EF update the null image to database in .Net MVC


asp.net-mvc,entity-framework,null,edit,httppostedfilebase
In my SachController, there is an Edit method like below. In the view, there are several textbox and one file-input for upload image object. Sometime, user doesn't want to change the image and they just don't select a new image. And the image (HttpPostedFileBase) is null. How can I avoid...

Unable to find the auto created Database


c#,asp.net,asp.net-mvc,entity-framework
I have created simple ASP.NET MVC4 application using EntityFramework Code first approach. The entity class is as below: public class Album { [Key] public int AblumId { get; set; } public decimal Price { get; set; } public string Title { get; set; } } public class MusicContext : DbContext...

How to use subquery result as the column name of another query


sql,oracle,plsql
I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example: select item, item_type ... forcast_0 * 0.9 as finalforcast forcast_0 * 0.8 as...

Get unmatched records without using oracle minus except not in


oracle,plsql,inner-join,outer-join
Actually I have two table and each having column name, I just want the result which are not there in Table2 Table1 ---- Name --- |A| |B| |C| |D| Table2 ------ |Name| ----- |A| |B| Answer |C| |D| I am able to do it by using minus select name from...

Extracting XML data from CLOB


sql,xml,oracle
How can I extract Food ItemID and Food Item Name and Quantity from the data as mentioned below. This is in clob column in plsql. <ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" Currency Id="43"/> </FoodItemDetails> <BillOption> <Bill Details Total Price="22222" BillOption="cash"/> </BillOption> <Authoritativeness/>...

How to use ajax to post json string to controller method?


jquery,asp.net-mvc,visual-studio-2013,asp.net-mvc-5
I want to be able to post a json string to a control action but it's always receive the string as null. If I create a view model for the controller method, it works, but that's not what I want since there will be too much view models to maintain....

Using MyBatis Update with foreach


sql,oracle,mybatis
i am struggling with update statement in MyBatis. I want to put multiple strings in one row: UPDATE MY_FILTERS SET GROUPS = <foreach item="item" collection="selectedGroups" open="" separator="," close=""> #{item} </foreach>, TEMPLATES = <foreach item="item" collection="selectedTemplates" open="" separator="," close=""> #{item} </foreach> where ID = #{id} I've tried the following alternatives: open...

Database object with different data


sql,asp.net,asp.net-mvc,database,entity-framework-6
I'm making a web page in ASP.NET MVC which compares prices from different shops. I have a one-to-many with products and the shops, where the SHOP has one PRODUCT and a PRODUCT has many SHOPs, the problem is that the product is the same but the price is different. Example:...

File IO Close() method error in ASP.NET MVC 6


asp.net-mvc,asp.net-mvc-6
I am doing a simple file IO in MVC6. I have added System.IO NuGet package. However, it gives me compile time error. VS IDE doesn't show any red mark when I type the code. The Close() method also appears in intellisense. Please help! My Code StreamWriter writer = System.IO.File.CreateText("some_valid_path"); writer.WriteLine("test");...

Entity Framework code-first: querying a view with no primary key


sql,oracle,entity-framework,view,ef-code-first
Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

How to format label in ASP.net chart control


c#,asp.net,asp.net-mvc,c#-4.0,reporting-services
I have created a column chart in asp.net. I am showing the date on x-axis. At the moment the date is shown as dd/mm/yyyy. All I need is to show the date as dd-mm-yy and ideally Weekday, dd-mm-yy e.g. Monday, 12-05-15. <asp:Chart ID="Chart1" runat="server" Height="400px" Width="900px" BorderWidth = "1"> <Series>...

Groovy - timestamp from minutes


oracle,grails,groovy,timestamp
I have an array or times/values coming back to be in an array like: [0, 60] Which are times in minutes, 0 = 12:00 a.m, 60 = 1:00 a.m. I am wanting to store these in an oracle database as timestamps. How do I convert minutes into timestamps in groovy?...

Result from pipelined function, always will sorted as “written”, or not?


sql,oracle,plsql,oracle12c
Needed get renumbered result set, for example: CREATE OR REPLACE TYPE nums_list IS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(from_n INTEGER, to_n INTEGER, cycle_max INTEGER) RETURN nums_list PIPELINED AS cycle_iteration INTEGER := from_n; BEGIN FOR i IN from_n..to_n LOOP PIPE ROW( cycle_iteration ); cycle_iteration := cycle_iteration + 1; IF...

Multiple Posted Types asp.net 5 MVC 6 API


c#,asp.net,asp.net-mvc,asp.net-5,asp.net-mvc-6
I can use [FromBody] for single type , but is there any way to use multiple? From the searching and reading I've done there is not a way, but i don't know if a way has been added in MVC 6. If not, where would be best to start with...

System.net.http.formatting causing issues with Newtonsoft.json


c#,asp.net,asp.net-mvc,json.net
My Windows service is in the same solution as a MVC project. The MVC project uses a reference to SignalR Client which requires Newtonsoft.Json v6 + the Windows service uses System.Net.Http.Formatting, which requires Newtonsoft.Json version 4.5.0.0. I assumed this would not be a problem, as I could just use a...

Cant delete in database because of constraints


c#,sql,asp.net,oracle
I am making an asp.net project and I am trying to delete a row in my database but that table got constraints. I am using this method to delete it.How can I rewrite it so that all constraints are disabled or something in order that i can properly delete a...

devenv.exe hogs CPU when debugging


asp.net-mvc,visual-studio-2013
Short version: When I debug ASP.NET MVC apps in VS2013 and try to edit razor views or css files, the CPU usage of devenv.exe skyrockets to the point where VS becomes unresponsive. Browser link is turned off, yet this still happens. Restarting debugging doesn't help, restarting VS doesn't help, restarting...

Identifier is too long


sql,oracle
Please help me to find why am getting identifier is too long error , even after having enough space for role in record. Exception : Error report - ORA-06550: line 14, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long ORA-06550: line 18, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long 06550. 00000...

Can I check if action is a child action in a view?


c#,asp.net-mvc,razor
I know that I can check if an action is a child action inside a controller, store the result in ViewBag or elsewhere and pass this information to the view, but assuming I don't want\can't modify the controller, is there some way to check if the current action is a...

How to hide columns in Kendo Grid when editing in a popup


asp.net-mvc,kendo-ui,kendo-grid,kendo-asp.net-mvc
I use Telerik Kendo Grid editing-inline. I want to hide certain columns when editing popup the number of columns....

ASP.NET httpHandlers & handlers


asp.net,asp.net-mvc
I am confused about httpHandlers in system.web and handlers in system.webServer. What is the difference between these two configuration? And how and when to use them? Actually another question is for modules as well: httpModules in system.web and modules in system.webServer...