LinQ查询 - 动态添加位置

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

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("Name") ascending
                         where (d.Field(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;
}

mattruma.. 7

这是我们如何做的一些示例代码......

    private void DataPortal_Fetch(GoalCriteria criteria)
    {
        using (var ctx = ContextManager
                    .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(row => row.Contact);
            opts.LoadWith(row => row.Sales);
            opts.LoadWith(row => row.Customer);
            ctx.DataContext.LoadOptions = opts;

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

我们只检查分配给我们的标准对象的空值,如果它不为null,那么我们将它附加到查询.

这是我们如何做的一些示例代码......

    private void DataPortal_Fetch(GoalCriteria criteria)
    {
        using (var ctx = ContextManager
                    .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(row => row.Contact);
            opts.LoadWith(row => row.Sales);
            opts.LoadWith(row => row.Customer);
            ctx.DataContext.LoadOptions = opts;

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

我们只检查分配给我们的标准对象的空值,如果它不为null,那么我们将它附加到查询.