System.Linq.Dynamic 动态查询

2019/03/02 12:58
阅读数 27
  • 安装
  • VS->工具栏->NuGet程序管理器,System.Linq.Dynamic 
  • 注意:

    使用动态查询必须先调用AsQueryable()方法,因为动态扩展仅适用于实现IQueryable的集合。然后迭代结果与常规Linq一样。

  • 动态查询
  • using System.Linq.Dynamic
    /// 得到明细过滤条件
            /// </summary>
            /// <returns></returns>
            private string GetFilterWhere(out  List<object> paramList)
            {
                StringBuilder strWhere = new StringBuilder();
                object items;
                paramList = new List<object>();
                strWhere.AppendFormat("ProductionDate >=@{0}", paramList.Count);
                paramList.Add(Convert.ToDateTime(startDetailDateEdit.EditValue).Date);
     
                strWhere.AppendFormat(" && ProductionDate <=@{0}", paramList.Count);
                paramList.Add(Convert.ToDateTime(endDetailDateEdit.EditValue).Date);
     
                items = factoryDetailCheckedComboBoxEdit.Properties.GetCheckedItems();
                if (items != null && items.ToString() != string.Empty)
                {//数组参数,数组中是否包含实例相等的FactoryID值,“outerIt 代表实例”
                    strWhere.AppendFormat(" && @{0}.Contains(outerIt.FactoryID)", paramList.Count);
                    paramList.Add(UIHelper.ToArray(items.ToString()));
                }
     
                if (cbCustomer.HasValue())
                {
                    strWhere.AppendFormat(" && CustomerID=@{0}", paramList.Count);
                    paramList.Add(Convert.ToInt32(cbCustomer.SelectedValue));
                }
     
                if (txtMachineCode.Text.Trim() != string.Empty)
                {
                    strWhere.AppendFormat(" && MachineCode.Contains(@{0})", paramList.Count);
                    paramList.Add(txtMachineCode.Text.Trim());
                }
     
                return strWhere.ToString();
            }
     
            //查询
            List<object> paramList;
            string strWhere = GetFilterWhere(out paramList);
     
            //根据条件查询
            var query = sourceList.AsQueryable<FacilityEfficiencyReportInfo>().Where(strWhere, paramList.ToArray());
            var resultList = (from dynamic g in query select g).ToList();
  • 动态分组
  • /// <summary>
            /// 得到分组的列名字符串
            /// </summary>
            /// <returns></returns>
            private string GetGroupNames()
            {
                StringBuilder groupName = new StringBuilder();
                if (m_FirstGroupFieldName != null)
                {
                    groupName.Append(m_FirstGroupFieldName.Key);
                }
                if (m_SecondGroupFieldName != null)
                {
                    if(groupName.Length>0)
                    {
                        groupName.AppendFormat(",{0}", m_SecondGroupFieldName.Key);
                    }
                    else
                    {
                        groupName.Append(m_SecondGroupFieldName.Key);
                    }
                }
                if (m_ThirdGroupFieldName != null)
                {
                    if(groupName.Length>0)
                    {
                        groupName.AppendFormat(",{0}", m_ThirdGroupFieldName.Key);
                    }
                    else
                    {
                        groupName.Append(m_ThirdGroupFieldName.Key);
                    }
                }
                return groupName.ToString();
            }
    object value;
                string groupNames = GetGroupNames();
                List<FacilityEfficiencyReportInfo> actualGroupList = new List<FacilityEfficiencyReportInfo>();//实际组列表,不区分日期
                if (string.IsNullOrEmpty(groupNames))
                {
                    actualGroupList = groupList.GroupBy(m => m.ID).Select(g => new FacilityEfficiencyReportInfo { FinishedAmount = g.Sum(m => m.FinishedAmount), Workers = g.Sum(m => m.Workers), Duration = g.Sum(m => m.Duration), Sam = g.Average(m => m.Sam),List=g.ToList() }).ToList();
                }
                else
                {
                    var query = groupList.AsQueryable()
                           .GroupBy(string.Format("new ({0})", groupNames), "it")
                           .Select("new(it.Key as Key, it as GroupList)");
                    var tempGroupList = (from dynamic g in query select g).ToList();
                    foreach (var g in tempGroupList)
                    {//每一组合并成一个FacilityEfficiencyReportInfo实例,明细放入model.List列表
                        FacilityEfficiencyReportInfo newModel = null;
                        object test = g.Key;
                        foreach (FacilityEfficiencyReportInfo m in g.GroupList)
                        {
                            if (newModel == null)
                            {
                                newModel = m.Clone();
                                newModel.InitDate();
                                newModel.List = new List<FacilityEfficiencyReportInfo>();
                            }
                            newModel.List.AddRange(m.List);
                        }
                        actualGroupList.Add(newModel);
                    }
                }
    query = Contact.GetContactsList().AsQueryable().Where("@0.Contains(outerIt.Country)", newList<String>() { "Austria", "Poland" });

    其中“outerIt”代表整理上下文:“联系人”列表。为此,我们应该定义一个“outerIt”关键字

    query = Contact.GetContactsList().AsQueryable().Where("@0.Contains(outerIt.Country) && it.BirthDate.Year > @1", new List<string>() { "Austria", "Poland" }, 1955);

     

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
在线直播报名
返回顶部
顶部