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