Dynamic LINQ "Where" clause...so close but having trouble

Question

User-901103060 posted

I am trying to build a dynamic LINQ where clause in a search page.  The user will have an option for filtering on 1-6 fields.  I have read many of the posts, blogs, etc. and I am close...but my "\"" doesn't seem to be working or something else?  I included the DynamicLibrary.cs file and System.Linq.Dynamic reference.

I created a class to store the conditions (fields and values) so that I can build the 'where' clause more easily.

class qryCondition
 {
       public qryCondition(string _fieldName, string _fieldValue)
       {
            FieldName = _fieldName;
            FieldValue = _fieldValue;
        }

        public string FieldName
        {
            get ;
            set ;
        }
        public string FieldValue
        {
            get ;
            set ;
        }
    }

I verify if there is something in the textbox, dropdown, etc. and add it to my List<qryCondition>.

// load "Where" info List<qryCondition>
List<qryCondition> conditions = new List<qryCondition>();
int index = 0;
                
if(!String.IsNullOrEmpty(txtIncidentNbr.Text))
{
 index = conditions.Count;
 conditions.Add(new qryCondition("\"IncidentNo.Contains(@"+index+")\"","\"" + txtIncidentNbr.Text +"\""));
}

....do this for all the fields for filtering (~6 fields)

Then I create the "Where" clause that puts the field names with @[Number] parameter holder first, and then add commas with the field valuse separating the parameters.

string qryWhere = "";
string qryParam = "";

foreach (qryCondition q in conditions)
{
  qryWhere += (String.IsNullOrEmpty(qryWhere)?"":"&&") + conditions[conditions.IndexOf(q)].FieldName;
  qryParam +=  (String.IsNullOrEmpty(qryWhere) ? "" : ",") +conditions[conditions.IndexOf(q)].FieldValue;
}

qryWhere += "," + qryParam;

The end result is when I hard code:

var qryWorks = context.IncidentHeaders.Where("IncidentNo.Contains(@0)", "955").ToList(); --this works

but

var qry = context.IncidentHeaders.Where(qryWhere).ToList(); --doesn't work

where qryWhere = "IncidentNo.Contains(@0)","955"

The error I receive is:

Expression of type 'Boolean' expected Source =IncidentReporting StackTrace= at System.Linq.Dynamic.ExpressionParser.Parse(Type resultType) in C:\data\vs2k8\DynamicLibrary.cs:line 689

Do you have any idea why this isn't working?  What should I do to troubleshoot this?  Any help is much appreciated.

Answer 1

User-311685349 posted

What I do, which is different to what you're doing, but may be worth a go is:

public IQueryable<someTable> SelectAll<TKey>(List<Expression<Func<someTable, bool>>> whereCls)
{
    var selectAllQry = from t in entities.someTable
                             select t;

    if (whereCls != null)
    {
        foreach(Expression<Func<someTable, bool>> whereStmt in whereCls)
        {
            selectAllQry = selectAllQry.Where(whereStmt);
        }
    }
}

 This works a treat.  You simply declare a "List<Expression<Func<someTable, bool>>>" and use LINQ to populate the list so it might look like this:

if (txtIncidentNbr.Text.length > 0)
{
    whereCls.Add(t => t.IncidentNo.Contains(txtIncidentNbr.Text);
}

 

Answer 2

User-901103060 posted

Andrew -

Awesome!  That did work and the code is much cleaner.  Thank you so much!

Piper

Answer 3

User-1145248021 posted

Superb! Works perfectly.