LinQ查询-动态添加位置 [英] LinQ query - Add Where dynamically

问题描述

我很难解决这个问题，需要在C#，asp.net中创建动态linq查询的代码.我有5个下拉列表，用于搜索同一数据库表中的不同列，并将项目过滤后的值返回到单个列表框.问题在于没有顺序在DDL中选择哪个或全部或任何一个，但是组合的过滤结果应显示在列表框中.我有一个有效的查询，该查询一次分别针对每个DDL选择在一个列中搜索并返回结果.必须使用AND添加where子句，才能向该查询动态添加其他DDL选择.谢谢

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

推荐答案

下面是一些示例代码...

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.

这篇关于LinQ查询-动态添加位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！