Dynamic column in where condition

Question

In my scenario, i want to query a table with a user selected column name. So every time column name for where condition will change, so I am trying to query on a query with lambda.

I exactly don't know how to do this, Please help me out of this. Or suggest if there is any other way to do this?

string[] criteria = searchCriteria.Split('_');
var columnName = criteria[0];                        
var columnValue = criteria[1];

var subQry = (from e in ctx.tblEmployee
              where (e.DateOfJoining <= startDate || (e.DateOfJoining.Value.Month == ApplyMonth && e.DateOfJoining.Value.Year == ApplyYear)) &&
                    monInputEmployee.Contains(e.Id) == flag
              select new
              {
                  e.Id,
                  e.Code,
                  e.FName,
                  e.DateOfJoining
              }).ToList();

var eParam = Expression.Parameter(typeof(EmployeeDetail), "e");
var comparison = Expression.And(
                 Expression.Equal(Expression.Property(eParam, columnName), Expression.Constant(columnValue)),
                 Expression.Equal(Expression.Property(eParam, "CompId"), Expression.Constant(compId)));

var lambda = Expression.Lambda<Func<EmployeeDetail, bool>>(comparison, eParam);
var mainQry = subQry.Where(lambda);

See https://msdn.microsoft.com/en-us/library/bb882637.aspx?f=255&MSPPError=-2147217396 — abatishchev, Feb 18 '15 at 04:44
And http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library (it currently doesn't work, here's the cached version http://webcache.googleusercontent.com/search?q=cache:ymdys--R3joJ:weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library) — abatishchev, Feb 18 '15 at 04:50

score 2 · Answer 1 · edited Feb 19 '15 at 13:28

@Anup, I strongly recommend you to build & execute the dynamic query before call .ToList() function. The database contexts contains IQueryable<TEntity> if you run any query, it will load all object into memory.

Function to build the dynamic expression:

static Expression<Func<T, bool>> EqualComparer<T>(string propertyName, object propertyValue)
where T : class
{
   var type = typeof (T);

   ParameterExpression input = Expression.Parameter(type);

   var prop = type.GetProperty(propertyName);

   var value = Convert.ChangeType(propertyValue, prop.PropertyType);

   var exProperty = Expression.Property(input, prop);

   var eqExpre = Expression.Equal(exProperty, Expression.Constant(value));

   return Expression.Lambda<Func<T, bool>>(eqExpre, input);
}

Check the following example(using in-memory objects)

public class Person
{
   public string Name { get; set; }
   public uint Age { get; set; }
}

private void TestMethod()
{
    //peoples collection
    var persons = new[]
    {
        new Person {Age = 10, Name = "Ten"},
        new Person {Age = 20, Name = "Twenty"},
        new Person {Age = 30, Name = "Thirty"},
        new Person {Age = 40, Name = "Forty"},
        new Person {Age = 50, Name = "Fifty"}
    };

    //test query
    const string column = "Age";
    const int value = 20;

    //build the expression & compile
    var expression = EqualComparer<Person>(column, value).Compile();

    //run the query
    var selected =
        from p in persons
        where p.Name.Length > 0 && expression(p)
        select p;

    //iterate through results
    foreach (var person in selected)
    {
        Debug.WriteLine(person.Name);
    }
}

I hope it helps you.

I got this error :- `The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.` — Anup, Feb 18 '15 at 09:40

score 2 · Answer 2 · answered Feb 18 '15 at 06:05

Here is useful article Build Where Clause Dynamically in Linq and take look at the PredicateBuilder by Joe Albahari. You can rewrite your Where clause with PredicateBuilder like this:

spouse you have SearchCriteria model.

Public class SearchCritera 
{
    public DateTime? DateOfJoining { get; set; }
    public int? CompanyId { get; set; }
    public int? CategoryId { get; set; }
    public EmployeeStatus? EmpStatus { get; set; }
}

var whereClause = PredicateBuilder.True<tblEmployee>();
if (searchCriteria.CompanyId.HasValue)
    whereClause = whereClause.And(r => r.CompanyId == searchCriteria.CompanyId);

if (searchCriteria.CategoryId.HasValue)
    whereClause = whereClause.And(r => r.Category == searchCriteria.CategoryId);

if (searchCriteria.CategoryId.HasValue)
    whereClause = whereClause.And(r => r.EmpStatus == searchCriteria.EmpStatus);

if (searchCriteria.DateOfJoining.HasValue)
{
    var dateClause1 = PredicateBuilder.True<tblEmployee>();
    dateClause1.And(r => r.DateOfJoining <= searchCriteria.DateOfJoining);

    var dateClause2 = PredicateBuilder.True<tblEmployee>();
    dateClause2.And(r => re.DateOfJoining.Value.Month == searchCriteria.DateOfJoining.Month);
    dateClause2.And(r => re.DateOfJoining.Value.Year == searchCriteria.DateOfJoining.Year);
    dateClause1.Or(dateClause2);

    whereClause.And(dateClause1)
}

var result = ctx.tblEmployee.AsExpandable().Where(whereClause);

I love the PredicateBuilder! So simple, yet effective. Thanks! — CodeOrElse, Jan 09 '17 at 22:37

score 2 · Answer 3 · answered Feb 24 '15 at 13:08

This is How i solved it, Just added one extra Where with lambda :-

string[] criteria = searchCriteria.Split('_');
var columnName = criteria[0];                        
var columnValue = criteria[1];

var eParam = Expression.Parameter(typeof(EmployeeDetail), "e");
var comparison = Expression.Equal(Expression.Property(eParam, columnName), Expression.Convert(Expression.Constant(columnValue), Expression.Property(eParam, columnName).Type));

var lambda = Expression.Lambda<Func<EmployeeDetail, bool>>(comparison, eParam);

var subQry = (from e in ctx.tblEmployee
              where (e.DateOfJoining <= startDate || (e.DateOfJoining.Value.Month == ApplyMonth && e.DateOfJoining.Value.Year == ApplyYear)) &&
                    monInputEmployee.Contains(e.Id) == flag
              select new
              {
                  e.Id,
                  e.Code,
                  e.FName,
                  e.DateOfJoining
              }).Where(lambda)
                .ToList();

Dynamic column in where condition

3 Answers3