Dynamic column in where condition

c# dynamicquery expression-trees linq

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);

Popular Answer

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);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow