I've got a Problem with Entity Framework 6 and i don't know how to solve it. I want to read data from a Table with EntityFramework. The Goal is to read that data with a where-clause which is filtering data from a specific column. The column I want to search for, is specified in the method-parameters.

An example: i have a table of persons

Normally i would select Data like this:

public List<Person> getData(string searchTerm)
{
    using (var db = new myDB())
    {
      return db.Person.Where(x=> x.Name == searchTerm).ToList();
    } 
}

But i want to be flexible also with the column i want to filter. Something like this:

public getData(string columnToSearch, string searchTerm)
{
     using (var db = new myDB())
    {
      return db.Person.Where(columnToSearch == searchTerm).ToList();
    } 
}

How can i do that?

I don't want to use Plain SQL and i can't edit the database.

Thanks for Helping.

CodePudding user response：

Given that you've only got 4 columns, I genuinely think I'd just:

public List<Person> GetData(string columnToSearch, string searchTerm)
{
    using var db = new myDB();
  
    if(columnToSearch == "Name")
      return db.Person.Where(p => p.Name == searchTerm).ToList();
    else if(columnToSearch == "FirstName")
      return db.Person.Where(p => p.FirstName == searchTerm).ToList();
    else if(columnToSearch == "Adress")
      return db.Person.Where(p => p.Adress == searchTerm).ToList();
    else if(columnToSearch == "Email")
      return db.Person.Where(p => p.Email == searchTerm).ToList();
    else
       throw ..
}

..or some similar choosing structure..

public List<Person> GetData(string columnToSearch, string searchTerm)
{
    using var db = new myDB();
  
    return (columnToSearch switch {
        "Name" => db.Person.Where(p => p.Name == searchTerm),
        "FirstName" => db.Person.Where(p => p.FirstName == searchTerm)
        "Adress" => db.Person.Where(p => p.Adress == searchTerm)
        "Email" => db.Person.Where(p => p.Email == searchTerm)
        _ => throw new ArgumentException(nameof(columnToSearch)   " should be one of: Name, FirstName, Adress, Email")
    }).ToList();
}

..could perhaps even switch the delegate that does the picking:

public List<Person> GetData(string columnToSearch, string searchTerm)
{
    Func<Person, bool> what = columnToSearch switch {
        "Name" => p => p.Name == searchTerm,
        "FirstName" => p => p.FirstName == searchTerm
        "Adress" => p => p.Adress == searchTerm
        "Email" => p => p.Email == searchTerm
        _ => throw new ArgumentException(nameof(columnToSearch)   " should be one of: Name, FirstName, Adress, Email")
    };

    using var db = new myDB();
    return db..Person.Where(what).ToList();
}

Even if you had 20 columns in this table, it's fairly easy to write repetitive code like this using a multi line editor:

That is an editor called Sublime, but VS does it too, either with ctrl alt click to place multiple cursors or by selecting some common thing and pressing shift alt .. If you use VS you'll want to install "Multiple Carets Booster" extension, otherwise pasting will behave crazy; in most editors that do multiple carets, if you have e.g. 20 carets and you paste the 20 lines (e.g. 20 column names) from the clipboard, you get one clip line per caret. In VS normally you get all 20 lines per caret (400 lines pasted) which makes it really hard work to have all 20 columns on your clipboard and paste them into the flow of code like the anim above does

CodePudding user response：

You can use the following extension method:

public static class QueryableExtensions
{
    public static IQueryable<T> FilterByColumn<T>(this IQueryable<T> query, string columnToSearch, string searchTerm)
    {
        var param = Expression.Parameter(typeof(T), "e");

        var body = Expression.Equal(
            Expression.PropertyOrFiled(param, columnToSearch),
            Expression.Constatnt(searchTerm));

        var filter = Expression.Lambda<Func<T, bool>>(body, param);

        return query.Where(filter);
    }
}

And usage:

public getData(string columnToSearch, string searchTerm)
{
     using (var db = new myDB())
    {
      return db.Person.FilterByColumn(columnToSearch, earchTerm).ToList();
    } 
}