LINQ where condition with dynamic column

Question

I have this code

// IQueryable<General> query

if (columnName == "Column1") 
{
  query = query.Where(x => x.Column1 == searchValue);
}
else if (columnName == "Column2") 
{
  query = query.Where(x => x.Column2 == searchValue);
}
else if (columnName == "Column3") 
{
  query = query.Where(x => x.Column3 == searchValue);
}
else if (columnName == "Column4") 
{
  query = query.Where(x => x.Column4 == searchValue);
}
// next zilions columns to come
// ...

and my question is. How can i past x.Column as a parameter inside ".Where" condition ?

_"next zilions columns to come"_ You should refactor your datamodel. Why you have so many similar columns? Use another table instead. Then your query also becomes very simple and more efficient. — Tim Schmelter, Oct 24 '18 at 10:49
you can't..not in any simple way..but couldn't you instead of passing (searchValue, columnName), pass `Expression> filterExpression` directly? used as `query.Where(filterExpression)` Where does the columnName, searchValue come from? why are they specified in that way — Vladi Pavelka, Oct 24 '18 at 11:07
@TimSchmelter its columns of report table.. each column contains total different data. The purpose is let the user filter each column individually. — Muflix, Oct 24 '18 at 11:29
All the answers so far are for Linq-to-Objects. You should have indicated if it has to work for some other provider. — bommelding, Oct 24 '18 at 11:33
@bommelding thank you, i edited description tags. Its in .net core project, so it is using enity framework and SQL database. — Muflix, Oct 24 '18 at 11:35
@bommelding actually my answer should work with Linq-To-Sql (because I'm working with Expressions) — Aleks Andreev, Oct 24 '18 at 11:36
@AleksAndreev - the proof of a pudding is in the eating... Does it actually work? — bommelding, Oct 24 '18 at 11:38

score 4 · Accepted Answer · answered Oct 24 '18 at 11:25

You can create a predicate manually. Use this method:

public static Expression<Func<General, bool>> CreatePredicate(string columnName, object searchValue)
{
    var xType = typeof(General);
    var x = Expression.Parameter(xType, "x");
    var column = xType.GetProperties().FirstOrDefault(p => p.Name == columnName);

    var body = column == null
        ? (Expression) Expression.Constant(true)
        : Expression.Equal(
            Expression.PropertyOrField(x, columnName),
            Expression.Constant(searchValue));

    return Expression.Lambda<Func<General, bool>>(body, x);
}

Now you can apply your predicate:

IQueryable<General> query = //
var predicate = CreatePredicate(columnName , searchValue);
query = query.Where(predicate);

Not entirely sure, but aren't you missing an `Expression.Convert` for the constant value? I think I had to use it some time ago or it would throw an exception. — Alexander Derck, Oct 24 '18 at 11:39

score 1 · Answer 2 · answered Oct 24 '18 at 11:24

You could use reflection and extension methods. As a rough example:

public class Foo
{
  public int Column1 { get; set; }
  public int Column2 { get; set; }
  ...
}

public static class FooExtensions
{
  // I would use the actual type here instead of object if you know the type.
  public static object GetProperyValue(this Foo foo, string columnName)
  {
    var propertyInfo = foo.GetType().GetProperty(columnName);
    var value = propertyInfo.GetValue(foo);
    // as well as cast value to the type
    return value;
  }
}

...

query = query.Where(x => x.GetProperyValue(columnName) == searchValue);
...

As a side note, that is not a well-designed query because every time you add a column to your model, you'd need to update your if-else. It violates the O in SOLID.

This won't work for example in Linq to Entities (I think we can assume the most used `IQueryProvider`), it can't translate the method to valid SQL — Alexander Derck, Oct 24 '18 at 11:29
@AlexanderDerck, yes, you are correct. That is a valid point. Did not see the first comment. — Dean, Oct 24 '18 at 13:14

score 0 · Answer 3 · answered Oct 24 '18 at 11:09

You can either use Reflection to retrieve the property via name

x.GetType().GetProperty(propertyName,BindingFlags).SetValue(x,value)
// propertyName = "Column1" for example
// BindingFlags are most likely Instance, Public and Property (IIRC)

or pass in the PropertyInfo directly into the method as a parameter. Your choice depends on the abstraction level you want to expose to consumers of your method.

LINQ where condition with dynamic column

3 Answers3

Linked