public Search OrderBy(IEnumerable <string> asc, IEnumerable <string> desc) { var orderBys = new List <OrderByOperation>(); foreach (var item in asc) { orderBys.Aggregate(OrderByOperation.None, (current, ob) => current && ob); orderBys.Add(new OrderByOperation(item, OrderByOperater.ASC)); } foreach (var item in desc) { orderBys.Add(new OrderByOperation(item, OrderByOperater.DESC)); } if (null == orderBys || !orderBys.Any()) { return(this); } var temporderby = orderBys.Aggregate(OrderByOperation.None, (current, ob) => current && ob); this._orderBy = temporderby; return(this); }
/// <summary> /// orderby /// </summary> /// <param name="orderBys"></param> /// <returns></returns> public Search OrderBy(params OrderByOperation[] orderBys) { if (null == orderBys || orderBys.Length <= 0) { return(this); } var temporderby = orderBys.Aggregate(OrderByOperation.None, (current, ob) => current && ob); this.orderBy = temporderby; return(this); }
/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public override Search CreatePageFromSection(Search fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); if (startIndex == 1) { return(base.CreatePageFromSection(fromSection, startIndex, endIndex)); } if (OrderByOperation.IsNullOrEmpty(fromSection.OrderByClip)) { foreach (Field f in fromSection.Fields) { if (!f.PropertyName.Equals("*")) { fromSection.OrderBy(f.Asc); break; } } } Check.Require(!OrderByOperation.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!"); if (fromSection.Fields.Count == 0) { fromSection.Select(Field.All); } fromSection.AddSelect(new Field(string.Concat("row_number() over(", fromSection.OrderByString, ") AS tmp_rowid"))); //OrderByClip tempOrderBy = fromSection.OrderByClip; fromSection.OrderBy(OrderByOperation.None); fromSection.TableName = string.Concat("(", fromSection.SqlString, ") AS tmp_table"); fromSection.Parameters = fromSection.Parameters; fromSection.DistinctString = string.Empty; fromSection.PrefixString = string.Empty; fromSection.GroupBy(GroupByOperation.None); fromSection.Select(Field.All); //fromSection.OrderBy(tempOrderBy); fromSection.Where(new WhereOperation(string.Concat("tmp_rowid BETWEEN ", startIndex.ToString(), " AND ", endIndex.ToString()))); return(fromSection); }
/// <summary> /// 设置默认主键排序 /// </summary> private void setPrimarykeyOrderby() { Field[] primarykeys = EntityCache.GetPrimaryKeyFields <T>(); OrderByOperation temporderBy; if (null != primarykeys && primarykeys.Length > 0) { temporderBy = new OrderByOperation(primarykeys[0]); } else { temporderBy = new OrderByOperation(EntityCache.GetFirstField <T>()); } OrderBy(temporderBy); }
/// <summary> /// 设置默认排序 /// </summary> private void SetDefaultOrderby() { if (!OrderByOperation.IsNullOrEmpty(this.OrderByClip)) { return; } if (_fields.Count > 0) { if (_fields.Any(f => f.PropertyName.Trim().Equals("*"))) { setPrimarykeyOrderby(); } } else { setPrimarykeyOrderby(); } }
/// <summary> /// orderby /// </summary> /// <param name="orderBy"></param> /// <returns></returns> public Search OrderBy(OrderByOperation orderBy) { this.orderBy = orderBy; return(this); }
/// <summary> /// /// </summary> /// <param name="orderBy"></param> /// <returns></returns> public new Search <T> OrderBy(OrderByOperation orderBy) { return((Search <T>)base.OrderBy(orderBy)); }
/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public virtual Search CreatePageFromSection(Search fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); int pageSize = endIndex - startIndex + 1; if (startIndex == 1) { fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); } else { if (OrderByOperation.IsNullOrEmpty(fromSection.OrderByClip)) { foreach (Field f in fromSection.Fields) { if (!f.PropertyName.Equals("*") && f.PropertyName.IndexOf('(') == -1) { fromSection.OrderBy(f.Asc); break; } } } Check.Require(!OrderByOperation.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!"); int count = fromSection.Count(fromSection); List <Parameter> list = fromSection.Parameters; if (endIndex > count) { int lastnumber = count - startIndex + 1; if (startIndex > count) { lastnumber = count % pageSize; } fromSection.PrefixString = string.Concat(" TOP ", lastnumber.ToString()); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByOperation); // fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS temp_table "); fromSection.PrefixString = string.Empty; fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByOperation.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByOperation); fromSection.Where(WhereOperation.All); } else { if (startIndex < count / 2) { fromSection.PrefixString = string.Concat(" TOP ", endIndex.ToString()); fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable "); fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByOperation.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByOperation); fromSection.Where(WhereOperation.All); // fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempOuttable "); fromSection.PrefixString = string.Empty; fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByOperation); } else { fromSection.PrefixString = string.Concat(" TOP ", (count - startIndex + 1).ToString()); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByOperation); fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable "); fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByOperation.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByOperation); fromSection.Where(WhereOperation.All); } } fromSection.Parameters = list; } return(fromSection); }