Entity Framework where clause filtering from specific column

Question

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

Name	FirstName	Adress	Email
Conner	Brian	New York	abc@abc.com
Schwarzenegger	Arnold	Los Angeles	abc@abc.com

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.

Answer 1

In EF Core 6, you can use EF.Property.

Thus, your query would be:

string field = "Name"; // You can pass dynamically your actual field here (e.g. "Email")
db.Person.Where(x => EF.Property<string>(x, field) == searchTerm).ToList();

Answer 2

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.PropertyOrField(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, searchTerm).ToList();
    } 
}

Answer 3

You are using entity framework, which can access your table of Persons. You'll probably have a DbContext that has a property to access this table:

public DbSet<Person> Persons {get; set;}

You also have two strings. One string represents the name of the Person property that you want to filter on, the other string represents the value that this property should have.

To make you code easier to read, easier to reuse, easier to maintain and unit test, my advice would be to split your problem into two subproblem:

• Translate the string columnToSearch (which in fact is the name of the column that you want to search), into a propertySelector, similar to the keySelector in GroupBy.
• Create an extension method that uses this KeySelector to select the property, and uses the searchTerm and Where to keep only those Persons that have equal value.

The nice thing about this separation, is that you can detect incorrect values for columnToSearch before you access the database. If you don't want to support certain properties, like foreign keys, you can leave them out. The method is easy to understand, easy to reuse, easy to unit test, and easy to change, for instance if you want to add a new column.

Expression<Func<Person, string>> ToPropertySelector(string columnName)
{
    switch (columName)
    {
        case nameof(Person.Name):
           return person => person.Name;
        case nameof(Person.FirstName):
           return person => person.FirstName;
        ...

        default:  // unsupported columnName
            throw new InvalidArgumentException(...);
    }
}

If your columns have other names than the ones that you want to communicate with your users, for instance, for instance if extenal uses think of LastName instead of name, you can easily change the procedure:

case "Name":
case "LastName":
    return person => person.Name;

Also if later the colum name changes, external users won't have to change.

Usage:

string columName = "Email";
string value = "MyName.Gmail.Com";

using (var dbContext = new MyDbContext(...))
{
    var propertySelector = ToPropertySelector(columnName);
    return dbContext.Persons
        .Where(person => propertySelector(person) == value)
        .ToList();
}

Of course, this will only work if your properties have a string as value. If you also want to support other property types, you need to create an extension method that has the proper type:

Expression<Func<Person, TProperty>> ToPropertySelector<TProperty>(string columnName)
{
    ... see above
}

public static IQueryable<Person> Where<TProperty>(IQueryable<Person> persons,
    string columName,
    TProperty value)
{
    var propertySelector = ToPropertySelector<TProperty>(columnName);
    return persons.Where(person => propertySelector(person) == value);
}

public static IQueryable<Person> Where<TProperty>(IQueryable<Person> persons,
    string columName,
    string valueTxt)
{
    Type propertyType = typeof(TProperty);
    TypeConverter converter = propertyType.GetConverter();

    TProperty value = converter.ConvertFromString(valueTxt)
    return persons.Where(columnName, value);
}

Usage:

DateTime birthDay = new DateTime(1993, 11, 23);
IQueryable<Person> persons = ...

var personsBornOnDate = persons.Where("BirthDay", birthDay);

Example of reuse: you don't have to use BirthDay as a string, you can use a property selector:

var personsBornOnDate = persons.Where(person => person.BirthDay, birthDay);

Nice thing of the latter is that the compiler will warn you if you use a non-existing property. The string version will only warn you at run time.

Answer 4

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