asked on

Pleasse help! Dynamic addtion of AND Where to linq query

EXperts,
I am having a hard time solving this problem, need code for creating a dynamic linq query in C#, asp.net. I have 5 dropdown list that searches different column in same database table and return item filtered value to a single listbox. The problem is there is no sequence that which or all or any wil be selected in DDLs but the combined filtered result should show up in listbox. I have a working query that is searching and returning result in one column at a time for each DDL selection separately. Have to add where clauses with AND to add other DDL selections dynamically to this query. Please help ASAP. Thx.

Please don't give me sample code, please modify mine to make query dynamic.  Here is that class method that searches for DDL selection in column and return list item to listbox.
 
public ListItemCollection searchProject(ListItemCollection projList, String searchstr, String columnName)
    {
        DataSet DSToReturn = new DataSet();
        
        
        ListItemCollection returnItems = new ListItemCollection();
        DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                             orderby d.Field<string>("Name") ascending
                             where (d.Field<string>(columnName) != null)
                             where d[columnName].ToString().ToLower().Contains(searchstr.ToLower())
                             select d).CopyToDataTable();
   
                
        foreach (ListItem li in projList)
        {
            if ((from System.Data.DataRow row in results.Rows
                 where li.Value.Equals(row["value"].ToString(), StringComparison.InvariantCultureIgnoreCase)
                 select row["value"]).Count() > 0)
                returnItems.Add(li);
        }
 
        return returnItems;
      
       
          }

Open in new window

suran78

ASKER

How Can I do someting like this with my query please help
if (txtFilter1.Text!="") qProducts=qProducts.Where(s=>s.Name==txtFilter1.Text);
if (txtFilter2.Text!="") qProducts=qProducts.Where(s=>s.Field==txtFilter2.Text);
if (cboCombo1.SelectedValue!=0) qProducts=qProducts.Where(s=>s.price...

JPJ78

One way to solve it would be to set the where condition for each textbox to check if the textbox is empty or if the text in the textbox matches the required field.
something like
(txtFilter1.Text!="" || s.Name==txtFilter1.Text)

which would give...
where (txtFilter1.Text!="" || s.Name==txtFilter1.Text) && (txtFilter2.Text!="" || s.Field==txtFilter2.Text) etc...

suran78

ASKER

I am very close, Please help me here how can I call your function CustomSearch in this method searchProject? I tried it an getting syntax error on where argument

public ListItemCollection searchProject(ListItemCollection projList, String searchstr, String columnName)
{
DataSet DSToReturn = new DataSet();

//ListItem item = projList.FindByText("ProjectName");
ListItemCollection returnItems = new ListItemCollection();
DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
orderby d.Field<string>("Name") ascending
where (d.Field<string>(columnName) != null)
&& d[columnName].ToString().ToLower().Contains(searchstr.ToLower())
select d).CopyToDataTable();
// HOW can I call ur function here?
//if (ProjDDL1.Text!="") results=results.Where(s=>s.Business==ProjDDL1.Text); <--SYNTAX ERROR
//if (ProjDDL2.Text!="") results=resuls.Where(s=>s.Business_group==ProjDDL2.Text);
//if (ProjDDL3.SelectedValue!=0) results=results.Where(s=>s.Value...

foreach (ListItem li in projList)
{
if ((from System.Data.DataRow row in results.Rows
where li.Value.Equals(row["value"].ToString(), StringComparison.InvariantCultureIgnoreCase)
select row["value"]).Count() > 0)
returnItems.Add(li);
}

return returnItems;

}

private DataTable CustomSearch<TKey>(Func<Records, bool> selector)
{
DataTable results = ((DataSet)_MyDataset).Tables["Records"].AsEnumerable().Where(selector).CopyToDataTable();
return results;
}

ASKER CERTIFIED SOLUTION

JPJ78

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JPJ78

I assumed that ProjDDL1.Text should have been ProjDDL1.SelectedValue (which represents the selected value in the dropdownlist)

suran78

ASKER

I do like this approach and this is adding filter dynamically, but I am getting syntax error, something minor is missing. How can I call teh CustomSearch function correctly?
public ListItemCollection searchProject(ListItemCollection projList, String searchstr, String columnName)
{
DataSet DSToReturn = new DataSet();

//ListItem item = projList.FindByText("ProjectName");
ListItemCollection returnItems = new ListItemCollection();
DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
orderby d.Field<string>("Name") ascending
where (d.Field<string>(columnName) != null)
&& d[columnName].ToString().ToLower().Contains(searchstr.ToLower())
select d).CopyToDataTable();
if (ProjDDL1.Text != "") results = results.CustomSearch<Records>(results, s => s.Business == ProjDDL1.Text); <-----SYNTAX ERROR on CustomSearch<Records>

foreach (ListItem li in projList)
{
if ((from System.Data.DataRow row in results.Rows
where li.Value.Equals(row["value"].ToString(), StringComparison.InvariantCultureIgnoreCase)
select row["value"]).Count() > 0)
returnItems.Add(li);
}

return returnItems;

}

private DataTable CustomSearch<TKey>(this DataTable dt, Func<Records, bool> selector)
{
DataTable results = (dt.AsEnumerable().Where(selector).CopyToDataTable());
return results;
}

suran78

ASKER

The ProjDDL1.SelectedItem.Value != "--" is not being recognized in class method
public ListItemCollection searchProject(ListItemCollection projList, String searchstr, String columnName)

How can I get the DDL value in the searchProject method?

suran78

ASKER

Do I have to pass all DDL values as parameter to SearchProject method? Is that the only option?

JPJ78

As I mentioned earlier, C# is not my main language so unfortunately that's the only option I can think of.

suran78

ASKER

I am tried dynamic expressions to add filter to linq query and its not working.
The solution that you suggested is dynamic? Can you please help me on this linq query?
I am passing all the DDL values to the class and now there won't be any columnName passed. Each DDL value is passed in an array myItems. and ColumnName will be hard coded?
So DDL1 value = this.myItems[0] ---> this value if is not "--" then search column "Business"
DDL2 = this.myItems[1] ----> this value if not "--" then search "Business_grp"
DDL3 = this.myItems[2]....----> if this if not "--" then search in column "ValueC"
...
What wil be the linQ query to add these conditions if they are not "--" i.e. user selects one, all or any.

suran78

ASKER

Finally I was able to solve it. attaching code

public ListItemCollection searchProject(ListItemCollection projList)
    {
        DataSet DSToReturn = new DataSet();       
        ListItemCollection returnItems = new ListItemCollection();
        ListItemCollection ResultItems = new ListItemCollection();
        DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                             orderby d.Field<string>("Name") ascending
                             select d).CopyToDataTable();
        foreach (DataRow row in results.Rows)
        {
            ListItem item = new ListItem(row["Name"].ToString(), row["Value"].ToString());
            ResultItems.Add(item);
        }
        
     
       
        
        if (this.myItems[0] != "--")
        {
 
            var query = (from q in results.AsEnumerable()
                         where q["Business"].ToString().Contains(this.myItems[0])
                         select q);
            if (query.Count() > 0)
                results = query.CopyToDataTable();
            else
                results.Rows.Clear();
 
            
            
        }
        if (this.myItems[1] != "--")
        {
 
            var query = (from q in results.AsEnumerable()
                         where q["Business_group"].ToString().Contains(this.myItems[1])
                         select q);
            if (query.Count() > 0)
                results = query.CopyToDataTable();
            else
                results.Rows.Clear();
 
        }
        if (this.myItems[2] != "--")
        {
 
            var query = (from q in results.AsEnumerable()
                         where q["Value_center"].ToString().Contains(this.myItems[2])
                         select q);
            if (query.Count() > 0)
                results = query.CopyToDataTable();
            else
                results.Rows.Clear();
 
        }
        if (this.myItems[3] != "--")
        {
 
           
            var query = (from q in results.AsEnumerable()
                         where q["Client_name"].ToString().Contains(this.myItems[3])
                         select q);
            if (query.Count() > 0)
                results = query.CopyToDataTable();
            else
                results.Rows.Clear();
 
        }
        if (this.myItems[4] != "--")
        {
 
            var query = (from q in results.AsEnumerable()
                         where q["Owner_name"].ToString().Contains(this.myItems[4])
                         select q);
            if (query.Count() > 0)
                results = query.CopyToDataTable();
            else
                results.Rows.Clear();           
            
 
        }
       
        if (results.Rows.Count > 0)
        {
            foreach (ListItem li in ResultItems)
            {
                if ((from System.Data.DataRow row in results.Rows
                     where li.Value.Equals(row["value"].ToString(), StringComparison.InvariantCultureIgnoreCase)
                     select row["value"]).Count() > 0)
                    returnItems.Add(li);
            }
        }
        else
        {
            ListItem item = new ListItem("No Results", "0");
            returnItems.Add(item);
        }
 
        return returnItems;      
       
        
    }

Open in new window

suran78

ASKER

Thanks for helping.