C# QueryBuilder类代码示例

        private void btnView_Click(object sender, EventArgs e)
        {
            if(KenhPhat._getSelectedID()<=1)
            {
                HelpMsgBox.ShowNotificationMessage("Vui lòng chọn kênh phát");
                return;
            }

            var sql =
                string.Format(
                    @"select ct.time_start,ct.time_end,ct.kenh_phat,iif(c.ct_id is null, tct.noi_dung, c.noi_dung) noi_dung,
            c.nuoc_san_xuat, ct.rate_group,datediff(minute,ct.time_start,ct.time_end) timesplit
            from tns_programs_grid_scr_ct ct
            left join tns_chuong_trinh tct on tct.tct_id=ct.chuong_trinh
            left join chuong_trinh c on c.ct_id=tct.ct_id
            where 1=1");
            var query = new QueryBuilder(sql);

            AppCtrl.addID(query, "ct.PGS_id", FileNguon);
            query.addID("CT.KENH_PHAT", KenhPhat._getSelectedID());
            var dt = NgayPhatSong.DateTime;
            query.addDateFromTo("CT.NGAY_PHAT", new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0),
                                new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59));

            query.setAscOrderBy("ct.time_start, ct.kenh_phat");

            var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
            gridControlDetail.DataSource = ds.Tables[0];
            gridBand1.Caption = dt.ToString("ddd " + FrameworkParams.option.dateFormat);
        }

 /// <summary>
 ///   FHA维修成本查询。
 /// </summary>
 /// <param name="query">查询表达式。</param>
 /// <returns> FHA维修成本DTO集合。</returns>
 public IQueryable<FhaMaintainCostDTO> FhaMaintainCostDTOQuery(
     QueryBuilder<FhaMaintainCost> query)
 {
     return
         query.ApplyTo(_maintainCostRepository.GetAll().OfType<FhaMaintainCost>())
             .Select(p => new FhaMaintainCostDTO
             {
                 Id = p.Id,
                 EngineProperty = p.EngineProperty,
                 Jx = p.Jx,
                 YearBudgetRate = p.YearBudgetRate,
                 Rate = p.Rate,
                 AirHour = p.AirHour,
                 HourPercent = p.HourPercent,
                 Hour = p.Hour,
                 FhaFeeUsd = p.FhaFeeUsd,
                 FhaFeeRmb = p.FhaFeeRmb,
                 Custom = p.Custom,
                 CustomAddedRmb = p.CustomAddedRmb,
                 TotalTax = p.TotalTax,
                 AddedValueRate = p.AddedValueRate,
                 AddedValue = p.AddedValue,
                 IncludeAddedValue = p.IncludeAddedValue,
                 CustomAdded = p.CustomAdded,
                 AircraftTypeId = p.AircraftTypeId,
                 MaintainInvoiceId = p.MaintainInvoiceId,
                 Year = p.Year,
                 AcutalBudgetAmount = p.MaintainInvoice.PaymentScheduleLineId == null ? 0 : p.MaintainInvoice.PaymentScheduleLine.Amount,
                 AcutalAmount = p.MaintainInvoice.InvoiceValue
             });
 }

 /// <summary>
 ///     add key=value into QueryModel.Items
 /// </summary>
 /// <param name="model">QueryModel</param>
 /// <param name="key">the name of Element (input/select/area/radio/checkbox)</param>
 /// <param name="val">the value of Element (input/select/area/radio/checkbox)</param>
 public static void AddSearchItem(QueryBuilder model, string key, string val)
 {
     if (model == null)
     {
         model = new QueryBuilder();
     }
     string orGroup = "";
     var keywords = key.Split("$', ')', '}".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
     if (keywords.Length != 2)
         return;
     var method = SearchMethodAdapter(keywords[0]);
     var field = keywords[1];
     if (string.IsNullOrEmpty(method)) return;
     if (!string.IsNullOrEmpty(field))
     {
         var item = new ConditionItem
         {
             Field = field,
             Value = val.Trim(),
             OrGroup = orGroup,
             Method = (QueryMethod)Enum.Parse(typeof(QueryMethod), method)
         };
         model.Items.Add(item);
     }
 }

 public override void AddSort(object expr, IComparer comparer)
 {
     // sort makes sense only when we are dealing with a query that
     // returns a nodeset.
     Query evalExpr;
     string query = expr as string;
     if (query != null)
     {
         evalExpr = new QueryBuilder().Build(query, out _needContext); // this will throw if expr is invalid
     }
     else
     {
         CompiledXpathExpr xpathExpr = expr as CompiledXpathExpr;
         if (xpathExpr != null)
         {
             evalExpr = xpathExpr.QueryTree;
         }
         else
         {
             throw XPathException.Create(SR.Xp_BadQueryObject);
         }
     }
     SortQuery sortQuery = _query as SortQuery;
     if (sortQuery == null)
     {
         _query = sortQuery = new SortQuery(_query);
     }
     sortQuery.AddSort(evalExpr, comparer);
 }

        /// <summary>
        ///     序号件装机历史查询。
        /// </summary>
        /// <param name="query">查询表达式。</param>
        /// <returns>序号件装机历史DTO集合。</returns>
        public IQueryable<SnHistoryDTO> SnHistoryDTOQuery(
            QueryBuilder<SnHistory> query)
        {
            var dbAircrafts = _unitOfWork.CreateSet<Aircraft>();

            return query.ApplyTo(_unitOfWork.CreateSet<SnHistory>()).Select(p => new SnHistoryDTO
            {
                Id = p.Id,
                Sn = p.Sn,
                SnRegId = p.SnRegId,
                Pn = p.Pn,
                PnRegId = p.PnRegId,
                CSN = p.CSN,
                TSN = p.TSN,
                CSN2 = p.CSN2,
                TSN2 = p.TSN2,
                Status = (int)p.Status,
                Position = (int)p.Position,
                AircraftId = p.AircraftId,
                ActionDate = p.ActionDate,
                ActionType = (int)p.ActionType,
                ActionNo = p.RemInstRecord.ActionNo,
                ActionReason = p.RemInstRecord.Reason,
                RemInstRecordId = p.RemInstRecordId,
                RegNumber = dbAircrafts.FirstOrDefault(c => c.Id == p.AircraftId).RegNumber,
            });
        }

 public IQueryable<MaintainGuaranteeDTO> MaintainGuaranteeQuery(QueryBuilder<Guarantee> query)
 {
     var dbCurrency = _unitOfWork.CreateSet<Currency>();
     var dbMaintainContract = _unitOfWork.CreateSet<MaintainContract>();
     return
         query.ApplyTo(_unitOfWork.CreateSet<Guarantee>()).OfType<MaintainGuarantee>()
             .Select(p => new MaintainGuaranteeDTO
             {
                 GuaranteeId = p.Id,
                 Amount = p.Amount,
                 CreateDate = p.CreateDate,
                 CurrencyId = p.CurrencyId,
                 EndDate = p.EndDate,
                 OperatorName = p.OperatorName,
                 MaintainContractId = p.MaintainContractId,
                 ReviewDate = p.ReviewDate,
                 Reviewer = p.Reviewer,
                 StartDate = p.StartDate,
                 SupplierId = p.SupplierId,
                 SupplierName = p.SupplierName,
                 Status = (int)p.Status,
                 CurrencyName = dbCurrency.FirstOrDefault(c => c.Id == p.CurrencyId).CnName,
                 MaintainContractName = dbMaintainContract.FirstOrDefault(c=>c.Id==p.MaintainContractId).Name,
             });
 }

 /// <summary>
 ///    贷项单查询
 /// </summary>
 /// <param name="query">查询表达式。</param>
 /// <returns>贷项单DTO集合。</returns>
 public IQueryable<MaintainCreditNoteDTO> MaintainCreditNoteDTOQuery(
     QueryBuilder<MaintainCreditNoteInvoice> query)
 {
     return
         query.ApplyTo(_unitOfWork.CreateSet<MaintainCreditNoteInvoice>())
              .Select(p => new MaintainCreditNoteDTO
              {
                  CreditNoteId = p.Id,
                  InvoiceNumber = p.InvoiceNumber,
                  InvoideCode = p.InvoideCode,
                  InvoiceDate = p.InvoiceDate,
                  SupplierName = p.SupplierName,
                  SupplierId = p.SupplierId,
                  InvoiceValue = p.InvoiceValue,
                  PaidAmount = p.PaidAmount,
                  OperatorName = p.OperatorName,
                  Reviewer = p.Reviewer,
                  CreateDate = p.CreateDate,
                  ReviewDate = p.ReviewDate,
                  IsValid = p.IsValid,
                  IsCompleted = p.IsCompleted,
                  Status = (int)p.Status,
                  CurrencyId = p.CurrencyId,
                  PaymentScheduleLineId = p.PaymentScheduleLineId,
                  InvoiceLines = p.InvoiceLines.Select(q => new InvoiceLineDTO
                  {
                      InvoiceLineId = q.Id,
                      ItemName = (int)q.ItemName,
                      Amount = q.Amount,
                      InvoiceId = q.InvoiceId,
                      OrderLineId = q.OrderLineId,
                      Note = q.Note,
                  }).ToList(),
              });
 }

 /// <summary>
 ///    采购合同飞机查询
 /// </summary>
 /// <param name="query">查询表达式。</param>
 /// <returns>采购合同飞机DTO集合。</returns>
 public IQueryable<PurchaseContractAircraftDTO> PurchaseContractAircraftDTOQuery(
     QueryBuilder<PurchaseContractAircraft> query)
 {
     return
     query.ApplyTo(_unitOfWork.CreateSet<ContractAircraft>().OfType<PurchaseContractAircraft>())
              .Select(p => new PurchaseContractAircraftDTO
              {
                  PurchaseContractAircraftId = p.Id,
                  ContractName = p.ContractName,
                  ContractNumber = p.ContractNumber,
                  RankNumber = p.RankNumber,
                  CSCNumber = p.CSCNumber,
                  SerialNumber = p.SerialNumber,
                  IsValid = p.IsValid,
                  ReceivedAmount = p.ReceivedAmount,
                  AcceptedAmount = p.AcceptedAmount,
                  AircraftTypeId = p.AircraftTypeId,
                  AircraftTypeName = p.AircraftType.Name,
                  PlanAircraftID = p.PlanAircraftID,
                  ImportCategoryId = p.ImportCategoryId,
                  ImportType = p.ImportCategory.ActionType,
                  ImportActionName = p.ImportCategory.ActionName,
                  SupplierId = p.SupplierId,
              });
 }

 public static void addID(QueryBuilder filter, string opLeft, PLMultiChoiceGrid input)
 {
     if (!input._IsNotCheckAnyOrCheckedAll)
     {
         filter.addID(opLeft, input._GetSelectedIds());
     }
 }

 /// <summary>
 ///     飞机配置查询。
 /// </summary>
 /// <param name="query">查询表达式。</param>
 /// <returns>飞机配置DTO集合。</returns>
 public IQueryable<AircraftConfigurationDTO> AircraftConfigurationDTOQuery(
     QueryBuilder<AircraftConfiguration> query)
 {
     return query.ApplyTo(_unitOfWork.CreateSet<AircraftConfiguration>()).Select(p => new AircraftConfigurationDTO
     {
         Id = p.Id,
         AircraftSeriesId = p.AircraftSeriesId,
         AircraftTypeId = p.AircraftTypeId,
         BEW = p.BEW,
         BW = p.BW,
         BWF = p.BWF,
         BWI = p.BWI,
         ConfigCode = p.ConfigCode,
         Description = p.Description,
         FileContent = p.FileContent,
         FileName = p.FileName,
         MCC = p.MCC,
         MLW = p.MLW,
         MMFW = p.MMFW,
         MTOW = p.MTOW,
         MTW = p.MTW,
         MZFW = p.MZFW,
         AircraftCabins = p.AircraftCabins.Select(q=>new AircraftCabinDTO
                                                  {
                                                      Id = q.Id,
                                                      AircraftCabinTypeId = q.AircraftCabinTypeId,
                                                      SeatNumber = q.SeatNumber,
                                                      Note = q.Note
                                                  }).ToList(),
     });
 }

 /// <summary>
 ///     <see cref="IOrderQuery" />
 /// </summary>
 /// <param name="query">
 ///     <see cref="IOrderQuery" />
 /// </param>
 /// <returns>
 ///     <see cref="IOrderQuery" />
 /// </returns>
 public IQueryable<AircraftLeaseOrderDTO> AircraftLeaseOrderQuery(QueryBuilder<Order> query)
 {
     var trades = _unitOfWork.CreateSet<Trade>();
     var result = query.ApplyTo(_orderRepository.GetAll().OfType<AircraftLeaseOrder>().Where(p => p.IsValid))
         .Select(o => new AircraftLeaseOrderDTO
         {
             Id = o.Id,
             Name = o.Name,
             Version = o.Version,
             CurrencyId = o.Currency.Id,
             OperatorName = o.OperatorName,
             OrderDate = o.OrderDate,
             Status = (int) o.Status,
             Note = o.Note,
             SupplierId = trades.FirstOrDefault(p => p.Id == o.TradeId).SupplierId,
             SupplierName = trades.FirstOrDefault(p => p.Id == o.TradeId).Supplier.CnName,
             AircraftLeaseOrderLines =
                 o.OrderLines.OfType<AircraftLeaseOrderLine>().Select(l => new AircraftLeaseOrderLineDTO
                 {
                     Id = l.Id,
                     UnitPrice = l.UnitPrice,
                     Amount = l.Amount,
                     Discount = l.Discount,
                     EstimateDeliveryDate = l.EstimateDeliveryDate,
                     Note = l.Note,
                     ContractAircraftId = l.ContractAircraftId,
                     OrderId = l.OrderId,
                     TotalLine = (l.UnitPrice*l.Amount)*(1 - (l.Discount/100M)),
                 }).ToList(),
         });
     return result;
 }

        public override QueryBuilder PLBuildQueryFilter()
        {
            QueryBuilder filter = new QueryBuilder(BIEN_MUC.FILTER_STRING);
            filter.addSoPhieu(BIEN_MUC.MA_BANG, MaBang.Text);
            filter.addLike("LOWER(" + BIEN_MUC.NOI_DUNG + ")", NoiDung.Text.ToLower());
            AppCtrl.addID(filter, BIEN_MUC.SO_DKCB_HD, SoDKHDCB);
            AppCtrl.addID(filter, BIEN_MUC.TIET_MUC, TietMuc);
            AppCtrl.addID(filter, BIEN_MUC.LOAI_LUU_TRU, LoaiBang);
            AppCtrl.addID(filter, BIEN_MUC.DON_VI_CUNG_CAP, DonViCungCap);
            AppCtrl.addID(filter, BIEN_MUC.DON_VI_SO_HUU, DonViSoHuu);
            filter.addIn(BIEN_MUC.POST_MASTER, AppCtrl.GetCheckedValues(TinhTrang, true));
            filter.addIn(BIEN_MUC.IS_THANH_LY, AppCtrl.GetCheckedValues(ThanhLy, true));
            AppCtrl.addFilterFromTo(filter, BIEN_MUC.NGAY_NHAP, ngayNhap);
            AppCtrl.addFilterFromTo(filter, BIEN_MUC.NAM_SAN_XUAT, NamSXTu.Value, NamSXDen.Value);
            long[] IDs = QuocGia._getSelectedIDs();
            if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
            {
                string cond = "(";
                foreach (long id in IDs)
                {
                    cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
                }
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond += ")";
                filter.addCondition(cond);
            }
            filter.addCondition("1=1");
            filter.setDescOrderBy(BIEN_MUC.NGAY_CAP_NHAT);

            return filter;
        }

 public static List<ParamField> getFields()
 {
     QueryBuilder query = new QueryBuilder(@"
             SELECT
                 rf.rdb$field_name, rf.rdb$description,
                 f.rdb$field_type, f.rdb$field_length
             FROM rdb$relation_fields rf
                 join rdb$relations r
                 on rf.rdb$relation_name = r.rdb$relation_name
                 join rdb$fields f
                 on f.rdb$field_name = rf.rdb$field_source
                 left join rdb$types t
                 on t.rdb$type = f.rdb$field_type
                 and t.rdb$field_name = 'RDB$FIELD_TYPE'
             WHERE
                 r.rdb$view_blr is null and
                 (r.rdb$system_flag is null or r.rdb$system_flag=0) and
                 rf.rdb$relation_name = 'FW_REPORT_PARAMS'
                 and rf.rdb$field_name <> '" + KEY_FIELD_NAME + "' and 1=1");
     DataSet ds = DABase.getDatabase().LoadDataSet(query);
     List<ParamField> list = new List<ParamField>();
     foreach (DataRow dr in ds.Tables[0].Rows)
     {
         ParamField field = new ParamField();
         field.FIELD_NAME = dr["RDB$FIELD_NAME"].ToString().Trim();
         field.DESCRIPTION = dr["RDB$DESCRIPTION"].ToString();
         field.FIELD_TYPE = HelpNumber.ParseInt32(dr["RDB$FIELD_TYPE"]);
         field.FIELD_LENGTH = HelpNumber.ParseInt64(dr["RDB$FIELD_LENGTH"]);
         list.Add(field);
     }
     return list;
 }

 public string BaseQuery(bool selectTop, int top)
 {
     QueryBuilder builder;
     try
     {
         builder = new QueryBuilder();
         if (selectTop == true)
             builder.SelectTop(top);
         else
             builder.Select();
         builder.AppendColumn(TaskObject.TASK_ID);
         builder.AppendColumn(TaskObject.TAG);
         builder.AppendColumn(TaskObject.CREATION_DATE);
         builder.AppendColumn(TaskObject.UPDATE_DATE);
         builder.AppendColumn(TaskObject.DUE_DATE);
         builder.AppendColumn(TaskObject.STATUS);
         builder.AppendColumn(TaskObject.PRIORITY);
         builder.AppendColumn(TaskObject.EFFORT);
         builder.AppendFromTable(TaskObject.TABLE);
         return builder.Query;
     }
     catch (System.Exception exception1)
     {
         Exception innerException = exception1;
         throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
     }
 }

 /// <summary>
 ///  付款通知查询。
 /// </summary>
 /// <param name="query">查询表达式。</param>
 /// <returns>付款通知DTO集合。</returns>
 public IQueryable<PaymentNoticeDTO> PaymentNoticeDTOQuery(QueryBuilder<PaymentNotice> query)
 {
     return
         query.ApplyTo(_paymentNoticeRepository.GetAll()).Select(p => new PaymentNoticeDTO
                          {
                              PaymentNoticeId = p.Id,
                              NoticeNumber = p.NoticeNumber,
                              CreateDate = p.CreateDate,
                              DeadLine = p.DeadLine,
                              SupplierName = p.SupplierName,
                              OperatorName = p.OperatorName,
                              Reviewer = p.Reviewer,
                              ReviewDate = p.ReviewDate,
                              Status = (int)p.Status,
                              SupplierId = p.SupplierId,
                              CurrencyId = p.CurrencyId,
                              CurrencyName = p.Currency.CnName,
                              BankAccountName = p.BankAccount.Account + "/" + p.BankAccount.Bank + p.BankAccount.Branch,
                              BankAccountId = p.BankAccountId,
                              IsComplete = p.IsComplete,
                              PaymentNoticeLines =
                                  p.PaymentNoticeLines.Select(q => new PaymentNoticeLineDTO
                                                                     {
                                                                         PaymentNoticeLineId = q.Id,
                                                                         InvoiceType = (int)q.InvoiceType,
                                                                         InvoiceId = q.InvoiceId,
                                                                         InvoiceNumber = q.InvoiceNumber,
                                                                         Amount = q.Amount,
                                                                         Note = q.Note
                                                                     }).ToList(),
                          });
 }