LinQ query - Add Where dynamically

Question

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 will 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. Thanks

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

duplicate http://stackoverflow.com/questions/848415/linq-dynamic-where-clause — Russell Steen, Oct 28 '09 at 14:52
How can I make this query dynamic to add where with each DDL selection, Please help me with this code. DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable() orderby d.Field("Name") ascending where (d.Field(columnName) != null) where d[columnName].ToString().ToLower().Contains(searchstr.ToLower()) select d).CopyToDataTable(); — menon, Oct 28 '09 at 17:07
possible duplicate of [Linq2SQL "or/and" operators (ANDed / ORed conditions)](http://stackoverflow.com/questions/1450983/linq2sql-or-and-operators-anded-ored-conditions) — Rune FS, Aug 03 '11 at 11:18

score 7 · Answer 1 · answered Oct 28 '09 at 14:46

Here's some example code for how we do it ...

    private void DataPortal_Fetch(GoalCriteria criteria)
    {
        using (var ctx = ContextManager<Data.ExodusDataContext>
                    .GetManager(Database.ApplicationConnection, false))
        {
            this.RaiseListChangedEvents = false;
            this.IsReadOnly = false;

            // set option to eager load child object(s)
            var opts = new System.Data.Linq.DataLoadOptions();
            opts.LoadWith<Data.Goal>(row => row.Contact);
            opts.LoadWith<Data.Goal>(row => row.Sales);
            opts.LoadWith<Data.Goal>(row => row.Customer);
            ctx.DataContext.LoadOptions = opts;

            IQueryable<Data.Goal> query = ctx.DataContext.Goals;

            if (criteria.Name != null) // Name
                query = query.Where(row => row.Name.Contains(criteria.Name));

            if (criteria.SalesId != null) // SalesId
                query = query.Where(row => row.SalesId == criteria.SalesId);

            if (criteria.Status != null) // Status
                query = query.Where(row => row.Status == (int)criteria.Status);

            if (criteria.Statuses.Count != 0) // Statuses
                query = query.Where(row => criteria.Statuses.Contains((GoalStatus)row.Status));

            if (criteria.ContactId != null) // ContactId
                query = query.Where(row => row.ContactId == criteria.ContactId);

            if (criteria.CustomerId != null) // CustomerId
                query = query.Where(row => row.CustomerId == criteria.CustomerId);

            if (criteria.ScheduledDate.DateFrom != DateTime.MinValue) // ScheduledDate
                query = query.Where(t => t.ScheduledDate >= criteria.ScheduledDate.DateFrom);
            if (criteria.ScheduledDate.DateTo != DateTime.MaxValue)
                query = query.Where(t => t.ScheduledDate <= criteria.ScheduledDate.DateTo);

            if (criteria.CompletedDate.DateFrom != DateTime.MinValue) // ComplatedDate
                query = query.Where(t => t.CompletedDate >= criteria.CompletedDate.DateFrom);
            if (criteria.CompletedDate.DateTo != DateTime.MaxValue)
                query = query.Where(t => t.CompletedDate <= criteria.CompletedDate.DateTo);

            if (criteria.MaximumRecords != null) // MaximumRecords
                query = query.Take(criteria.MaximumRecords.Value);

            var data = query.Select(row => GoalInfo.FetchGoalInfo(row));

            this.AddRange(data);

            this.IsReadOnly = true;
            this.RaiseListChangedEvents = true;
        }
    }

We just check for a null value assigned to our criteria object, if it's not null then we append it to the query.

I am absolutely new, please help, I copied the whole Dataportal_Fetch function in Class file and getting syntax error in every line. Am I missing anything? and How do I call this function in code behind? — menon, Oct 28 '09 at 16:43
In my case I have _MyDataset that is being searched by linq Query, How do I use that instead of using (var ctx = ContextManager .GetManager(Database.ApplicationConnection, false)) — menon, Oct 28 '09 at 16:45
here is my query: how Can I add it to ur code? DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable() orderby d.Field("Name") ascending where (d.Field(columnName) != null) where d[columnName].ToString().ToLower().Contains(searchstr.ToLower()) select d).CopyToDataTable(); — menon, Oct 28 '09 at 17:04

LinQ query - Add Where dynamically

1 Answers1

Linked