Click here to Skip to main content
14,691,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 29-Apr-20 8:48am
v2

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
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.
var search = db.EmpDatas.Where(p => p.JobTitle.Contains(parameter));
What I would think think you actually want for a query is
SELECT *
FROM   EmpDatas
WHERE  JobTitle LIKE '%parameter%'
   
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
You're going to need to build a dynamic expression. Add a using directive for the System.Linq.Expressions[^] namespace.
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());
}
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900