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

Answer 1

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;


      }

Answer 2

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.