How to search in database by column name in MVC

Question

0.00/5 (No votes)

See more:

public ActionResult Search(string column , string parameter)
        {

            var search = db.EmpDatas.Where(p => p.JobTitle.Contains(parameter));      
            
            return View("insurance", search.ToList());
           
        }

this is my code i want to put parameter "column" instead of "JobTitle" that in where condition

that mean user search on column name and data in that column and retrieve a list

What I have tried:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'EmpDatas' and COLUMN_NAME = 'JobTitle'

i try this SQL query in SQLserver put i don't know ho i put it in MVC controller

Posted 26-Apr-20 5:04am

Member 13058758

Updated 29-Apr-20 7:48am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2020-04-26T07:08:00

Solution 1

This is one those queries that just makes me want to shake my head; as you are searching for COLUMN_NAME which you already know.
The 2 choices for the result of this query are either going to be NULL or the name of the column name you are searching for

SQL

SELECT COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME  = N'EmpDatas'
AND    COLUMN_NAME = N'JobTitle'

It just does not make sense to me when compare to the C# call which looks like a search of the EmpData table for an employee who has a job title containing the text contained in parameter.

C#

var search = db.EmpDatas.Where(p => p.JobTitle.Contains(parameter));

What I would think think you actually want for a query is

SQL

SELECT *
FROM   EmpDatas
WHERE  JobTitle LIKE '%parameter%'

Posted 26-Apr-20 7:08am

MadMyche

Comments

Member 13058758 26-Apr-20 19:07pm

No I want to search with column name instead of jobTitle |Set parameter

MadMyche 26-Apr-20 19:14pm

Have you though about creating a Model based on that Info Schema table?

Member 13058758 26-Apr-20 20:20pm

I have a model put how I use it ?

MadMyche 26-Apr-20 20:38pm

For starters, I would look to see how your other Models (eg EmpData) and Controllers are working with the DB

Member 13058758 27-Apr-20 7:31am

i work with entity framework, i don't understand what do you mean with how other models work ?

MadMyche 27-Apr-20 9:21am

Then you are going to need to learn how to either:
1. Add new Models via adding to Entity Framework
2. Add Models and database connections manually.

Both of which require more than a "quick answer" allows and would require more of a tutorial if you don't understand the basic concepts

Richard Deeming · Answer 2 · 2020-04-29T07:50:00

You're going to need to build a dynamic expression. Add a using directive for the System.Linq.Expressions[^] namespace.

C#

private static Expression<Func<TEntity, bool>> BuildDynamicSearchFilter<TEntity>(string column, string value)
{
    var p = Expression.Parameter(typeof(TEntity), "p");
    
    var propertyToSearch = Expression.Property(p, column);
    if (propertyToSearch.Type != typeof(string))
    {
        throw new ArgumentException($"Column '{column}' is a {propertyToSearch.Type}, and cannot be searched", nameof(column));
    }
    
    var containsMethod = typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) });
    var valueToFind = Expression.Constant(value);
    var body = Expression.Call(propertyToSearch, containsMethod, valueToFind);
    return Expression.Lambda<Func<TEntity, bool>>(body, p);
}

public ActionResult Search(string column, string parameter)
{
    // TODO: Check that this is the correct model type:
    var filter = BuildDynamicSearchFilter<EmpData>(column, parameter);
    var search = db.EmpDatas.Where(filter);
    return View("insurance", search.ToList());
}