protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { switch (node.Operator) { case SqlColumnConstraintOperator.Equal: case SqlColumnConstraintOperator.NotEqual: //在 Oracle 中,空字符串的对比,需要转换为对 Null 值的对比。 var strValue = node.Value as string; if (strValue != null && strValue.Length == 0) { node.Value = null; } break; default: break; } return base.VisitSqlColumnConstraint(node); }
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { switch (node.Operator) { case SqlColumnConstraintOperator.Equal: case SqlColumnConstraintOperator.NotEqual: //在 Oracle 中,空字符串的对比,需要转换为对 Null 值的对比。 var strValue = node.Value as string; if (strValue != null && strValue.Length == 0) { node.Value = null; } break; default: break; } return(base.VisitSqlColumnConstraint(node)); }
/// <summary> /// 转换空值比较 /// </summary> /// <param name="node"></param> /// <returns></returns> protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { switch (node.Operator) { case SqlColumnConstraintOperator.Equal: case SqlColumnConstraintOperator.NotEqual: //在 MySql 中,空字符串的对比,需要转换为对 Null 值的对比。 if (node.Value is string) { if (node.Value != null) { if (node.Value.ToString().Length == 0) { node.Value = ""; } } } break; //case SqlColumnConstraintOperator.Contains: // if (node.Value is string && node.Value.ToString().IndexOf('%')>=0) // { // node.Value = "locate('" + node.Value + "'," + node.Column.ColumnName + ")!=0"; // } // break; //case SqlColumnConstraintOperator.NotContains: // if (node.Value is string&& node.Value.ToString().IndexOf('%') >= 0) // { // node.Value = "locate('" + node.Value + "'," + node.Column.ColumnName + ") = 0"; // } // break; default: break; } return(base.VisitSqlColumnConstraint(node)); }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> private ISqlSelect ModifyToPagingTreeWithNotIn(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } //如果是第一页,则只需要使用 TOP 语句即可。 if (pagingInfo.PageNumber == 1) { return(new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + pagingInfo.PageSize + " " }, raw.Selection ?? SqlSelectAll.Default }, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy }); } /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * 转换分页后: * * SELECT TOP 10 * * FROM ASN * WHERE ASN.Id > 0 AND ASN.Id NOT IN( * SELECT TOP 20 Id * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * ) * ORDER BY ASN.AsnCode ASC * **********************************************************************/ //先要找到主表的 PK,分页时需要使用此主键列来生成分页 Sql。 //这里约定 Id 为主键列名。 var finder = new FirstTableFinder(); var pkTable = finder.Find(raw.From); var pkColumn = new SqlColumn { Table = pkTable, ColumnName = EntityConvention.IdColumnName }; //先生成内部的 Select var excludeSelect = new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " " }, pkColumn }, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy, }; var res = new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + pagingInfo.PageSize + " " }, raw.Selection ?? SqlSelectAll.Default }, From = raw.From, OrderBy = raw.OrderBy, }; var newWhere = new SqlColumnConstraint { Column = pkColumn, Operator = SqlColumnConstraintOperator.NotIn, Value = excludeSelect }; if (raw.Where != null) { res.Where = new SqlBinaryConstraint { Left = raw.Where, Opeartor = SqlBinaryConstraintType.And, Right = newWhere }; } else { res.Where = newWhere; } return(res); }
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { var op = node.Operator; var value = node.Value; value = this.PrepareConstraintValue(value); #region 处理一些特殊的值 switch (op) { case SqlColumnConstraintOperator.Like: case SqlColumnConstraintOperator.Contains: case SqlColumnConstraintOperator.StartsWith: case SqlColumnConstraintOperator.EndsWith: //如果是空字符串的模糊对比操作,直接认为是真。 var strValue = value as string; if (string.IsNullOrEmpty(strValue)) { _sql.Append("1 = 1"); return node; } break; case SqlColumnConstraintOperator.NotLike: case SqlColumnConstraintOperator.NotContains: case SqlColumnConstraintOperator.NotStartsWith: case SqlColumnConstraintOperator.NotEndsWith: //如果是空字符串的模糊对比操作,直接认为是假。 var strValue2 = value as string; if (string.IsNullOrEmpty(strValue2)) { _sql.Append("1 != 1"); return node; } break; case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理: //In(Empty) 表示 false,NotIn(Empty) 表示 true。 if (value is IEnumerable) { bool hasValue = false; foreach (var item in value as IEnumerable) { hasValue = true; break; } if (!hasValue) { if (op == SqlColumnConstraintOperator.In) { _sql.Append("0 = 1"); } else { _sql.Append("1 = 1"); } return node; } } break; default: break; } #endregion this.AppendColumnUsage(node.Column); //根据不同的操作符,来生成不同的_sql。 switch (op) { case SqlColumnConstraintOperator.Equal: if (value == null || value == DBNull.Value) { _sql.Append(" IS NULL"); } else { _sql.Append(" = "); _sql.AppendParameter(value); } break; case SqlColumnConstraintOperator.NotEqual: if (value == null || value == DBNull.Value) { _sql.Append(" IS NOT NULL"); } else { _sql.Append(" != "); _sql.AppendParameter(value); } break; case SqlColumnConstraintOperator.Greater: _sql.Append(" > "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.GreaterEqual: _sql.Append(" >= "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Less: _sql.Append(" < "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.LessEqual: _sql.Append(" <= "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Like: _sql.Append(" LIKE "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.NotLike: _sql.Append(" NOT LIKE "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Contains: _sql.Append(" LIKE "); _sql.AppendParameter("%" + value + "%"); break; case SqlColumnConstraintOperator.NotContains: _sql.Append(" NOT LIKE "); _sql.AppendParameter("%" + value + "%"); break; case SqlColumnConstraintOperator.StartsWith: _sql.Append(" LIKE "); _sql.AppendParameter(value + "%"); break; case SqlColumnConstraintOperator.NotStartsWith: _sql.Append(" NOT LIKE "); _sql.AppendParameter(value + "%"); break; case SqlColumnConstraintOperator.EndsWith: _sql.Append(" LIKE "); _sql.AppendParameter("%" + value); break; case SqlColumnConstraintOperator.NotEndsWith: _sql.Append(" NOT LIKE "); _sql.AppendParameter("%" + value); break; case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN"; _sql.Append(" ").Append(opSql).Append(" ("); if (value is IEnumerable) { bool first = true; bool needDelimiter = false; int i = 0; foreach (var item in value as IEnumerable) { if (++i > this.MaxItemsInInClause) throw new TooManyItemsInInClauseException(); if (first) { first = false; needDelimiter = item is string || item is DateTime || item is Guid; } else { _sql.Append(','); } if (needDelimiter) { _sql.Append('\'').Append(item).Append('\''); } else { _sql.Append(item); } //由于集合中的数据可能过多,所以这里不要使用参数化的查询。 //_sql.AppendParameter(item); } } else if (value is SqlNode) { _sql.AppendLine(); this.Indent++; this.Visit(value as SqlNode); this.Indent--; _sql.AppendLine(); } _sql.Append(')'); break; default: throw new NotSupportedException(); } return node; }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> private ISqlSelect ModifyToPagingTree_With_NotIn(SqlSelect raw, PagingInfo pagingInfo) { /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * 转换分页后: * * SELECT TOP 10 * * FROM ASN * WHERE ASN.Id > 0 AND ASN.Id NOT IN( * SELECT TOP 20 Id * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * ) * ORDER BY ASN.AsnCode ASC * **********************************************************************/ //先要找到主表的 PK,分页时需要使用此主键列来生成分页 Sql。 //这里约定 Id 为主键列名。 var finder = new FirstTableFinder(); var pkTable = finder.Find(raw.From); var pkColumn = new SqlColumn { Table = pkTable, ColumnName = EntityConvention.IdColumnName }; //先生成内部的 Select var excludeSelect = new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " " }, pkColumn }, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy, }; var res = new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + pagingInfo.PageSize + " " }, raw.Selection ?? SqlSelectAll.Default }, From = raw.From, OrderBy = raw.OrderBy, }; //where var newWhere = new SqlColumnConstraint { Column = pkColumn, Operator = SqlColumnConstraintOperator.NotIn, Value = excludeSelect }; if (raw.Where != null) { res.Where = new SqlBinaryConstraint { Left = raw.Where, Opeartor = SqlBinaryConstraintType.And, Right = newWhere }; } else { res.Where = newWhere; } return(res); }
protected virtual SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { this.Visit(node.Column); return node; }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pkColumn">需要指定主键列</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } //如果是第一页,则只需要使用 TOP 语句即可。 if (pagingInfo.PageNumber == 1) { raw.Top = pagingInfo.PageSize; return(raw); } /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * SELECT TOP 10 * * FROM ASN * WHERE ASN.Id > 0 AND ASN.Id NOT IN( * SELECT TOP 20 Id * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * ) * ORDER BY ASN.AsnCode ASC * **********************************************************************/ var excludeSelect = new SqlSelect { Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize, Selection = pkColumn, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy, }; var res = new SqlSelect { Top = pagingInfo.PageSize, Selection = raw.Selection, From = raw.From, OrderBy = raw.OrderBy, }; var newWhere = new SqlColumnConstraint { Column = pkColumn, Operator = SqlColumnConstraintOperator.NotIn, Value = excludeSelect }; if (raw.Where != null) { res.Where = new SqlBinaryConstraint { Left = raw.Where, Opeartor = SqlBinaryConstraintType.And, Right = newWhere }; } else { res.Where = newWhere; } return(res); }
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { var op = node.Operator; var value = node.Value; #region 处理一些特殊的值 switch (op) { case SqlColumnConstraintOperator.Like: case SqlColumnConstraintOperator.Contains: case SqlColumnConstraintOperator.StartWith: case SqlColumnConstraintOperator.EndWith: //如果是空字符串的模糊对比操作,直接认为是真。 var strValue = value as string; if (string.IsNullOrEmpty(strValue)) { _sql.Append("1 = 1"); return(node); } break; case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理: //In(Empty) 表示 false,NotIn(Empty) 表示 true。 if (value is IEnumerable) { bool hasValue = false; foreach (var item in value as IEnumerable) { hasValue = true; break; } if (!hasValue) { if (op == SqlColumnConstraintOperator.In) { _sql.Append("0 = 1"); } else { _sql.Append("1 = 1"); } return(node); } } break; default: break; } #endregion this.AppendColumnUsage(node.Column); //根据不同的操作符,来生成不同的_sql。 switch (op) { case SqlColumnConstraintOperator.Equal: if (value == null || value == DBNull.Value) { _sql.Append(" IS NULL"); } else { _sql.Append(" = "); _sql.AppendParameter(value); } break; case SqlColumnConstraintOperator.NotEqual: if (value == null || value == DBNull.Value) { _sql.Append(" IS NOT NULL"); } else { _sql.Append(" != "); _sql.AppendParameter(value); } break; case SqlColumnConstraintOperator.Greater: _sql.Append(" > "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.GreaterEqual: _sql.Append(" >= "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Less: _sql.Append(" < "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.LessEqual: _sql.Append(" <= "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Like: _sql.Append(" LIKE "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Contains: _sql.Append(" LIKE "); _sql.AppendParameter("%" + value + "%"); break; case SqlColumnConstraintOperator.StartWith: _sql.Append(" LIKE "); _sql.AppendParameter(value + "%"); break; case SqlColumnConstraintOperator.EndWith: _sql.Append(" LIKE "); _sql.AppendParameter("%" + value); break; case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN"; _sql.Append(" ").Append(opSql).Append(" ("); if (value is IEnumerable) { bool first = true; foreach (var item in value as IEnumerable) { if (!first) { _sql.Append(','); } _sql.AppendParameter(item); first = false; } } else if (value is SqlNode) { _sql.AppendLine(); this.Indent++; this.Visit(value as SqlNode); this.Indent--; _sql.AppendLine(); } _sql.Append(')'); break; default: throw new NotSupportedException(); } return(node); }
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { var op = node.Operator; var value = node.Value; value = this.PrepareConstraintValue(value); #region 处理一些特殊的值 switch (op) { case SqlColumnConstraintOperator.Like: case SqlColumnConstraintOperator.Contains: case SqlColumnConstraintOperator.StartsWith: case SqlColumnConstraintOperator.EndsWith: //如果是空字符串的模糊对比操作,直接认为是真。 var strValue = value as string; if (string.IsNullOrEmpty(strValue)) { _sql.Append("1 = 1"); return(node); } break; case SqlColumnConstraintOperator.NotLike: case SqlColumnConstraintOperator.NotContains: case SqlColumnConstraintOperator.NotStartsWith: case SqlColumnConstraintOperator.NotEndsWith: //如果是空字符串的模糊对比操作,直接认为是假。 var strValue2 = value as string; if (string.IsNullOrEmpty(strValue2)) { _sql.Append("1 != 1"); return(node); } break; case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理: //In(Empty) 表示 false,NotIn(Empty) 表示 true。 if (value is IEnumerable) { bool hasValue = false; foreach (var item in value as IEnumerable) { hasValue = true; break; } if (!hasValue) { if (op == SqlColumnConstraintOperator.In) { _sql.Append("0 = 1"); } else { _sql.Append("1 = 1"); } return(node); } } break; default: break; } #endregion this.AppendColumnUsage(node.Column); //根据不同的操作符,来生成不同的_sql。 switch (op) { case SqlColumnConstraintOperator.Equal: if (value == null || value == DBNull.Value) { _sql.Append(" IS NULL"); } else { _sql.Append(" = "); _sql.AppendParameter(value); } break; case SqlColumnConstraintOperator.NotEqual: if (value == null || value == DBNull.Value) { _sql.Append(" IS NOT NULL"); } else { _sql.Append(" != "); _sql.AppendParameter(value); } break; case SqlColumnConstraintOperator.Greater: _sql.Append(" > "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.GreaterEqual: _sql.Append(" >= "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Less: _sql.Append(" < "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.LessEqual: _sql.Append(" <= "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Like: _sql.Append(" LIKE "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.NotLike: _sql.Append(" NOT LIKE "); _sql.AppendParameter(value); break; case SqlColumnConstraintOperator.Contains: _sql.Append(" LIKE "); _sql.AppendParameter(WILDCARD_ALL + this.Escape(value) + WILDCARD_ALL); this.AppendEscapePlause(value); break; case SqlColumnConstraintOperator.NotContains: _sql.Append(" NOT LIKE "); _sql.AppendParameter(WILDCARD_ALL + this.Escape(value) + WILDCARD_ALL); this.AppendEscapePlause(value); break; case SqlColumnConstraintOperator.StartsWith: _sql.Append(" LIKE "); _sql.AppendParameter(this.Escape(value) + WILDCARD_ALL); this.AppendEscapePlause(value); break; case SqlColumnConstraintOperator.NotStartsWith: _sql.Append(" NOT LIKE "); _sql.AppendParameter(this.Escape(value) + WILDCARD_ALL); this.AppendEscapePlause(value); break; case SqlColumnConstraintOperator.EndsWith: _sql.Append(" LIKE "); _sql.AppendParameter(WILDCARD_ALL + this.Escape(value)); this.AppendEscapePlause(value); break; case SqlColumnConstraintOperator.NotEndsWith: _sql.Append(" NOT LIKE "); _sql.AppendParameter(WILDCARD_ALL + this.Escape(value)); this.AppendEscapePlause(value); break; case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN"; _sql.Append(" ").Append(opSql).Append(" ("); if (value is IEnumerable) { bool first = true; bool needDelimiter = false; int i = 0; foreach (var item in value as IEnumerable) { if (++i > this.MaxItemsInInClause) { throw new TooManyItemsInInClauseException(); } if (first) { first = false; needDelimiter = item is string || item is DateTime || item is Guid; } else { _sql.Append(','); } if (needDelimiter) { _sql.Append('\'').Append(item).Append('\''); } else { _sql.Append(item); } //由于集合中的数据可能过多,所以这里不要使用参数化的查询。 //_sql.AppendParameter(item); } } else if (value is SqlNode) { _sql.AppendLine(); this.Indent++; this.Visit(value as SqlNode); this.Indent--; _sql.AppendLine(); } _sql.Append(')'); break; default: throw new NotSupportedException(); } return(node); }
/// <summary> /// 重写VisitSqlColumnConstraint方法 /// 处理In、NotIn操作,如果传入的值为字符串,则查询条件增加N前缀 /// </summary> /// <param name="node"></param> /// <returns></returns> protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node) { var op = node.Operator; var value = node.Value; value = this.DbTypeCoverter.ToDbParameterValue(value); #region 处理一些特殊的值 switch (op) { case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理: //In(Empty) 表示 false,NotIn(Empty) 表示 true。 if (value is IEnumerable) { bool hasValue = false; foreach (var item in value as IEnumerable) { hasValue = true; break; } if (!hasValue) { if (op == SqlColumnConstraintOperator.In) { Sql.Append("0 = 1"); } else { Sql.Append("1 = 1"); } return(node); } } break; default: break; } #endregion //根据不同的操作符,来生成不同的Sql。 switch (op) { case SqlColumnConstraintOperator.In: case SqlColumnConstraintOperator.NotIn: var column = node.Column; base.AppendColumnUsage(node.Column); var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN"; Sql.Append(" ").Append(opSql).Append(" ("); if (value is IEnumerable) { bool first = true; bool needDelimiter = false; bool isString = false; int i = 0; foreach (var item in value as IEnumerable) { if (++i > this.MaxItemsInInClause) { throw new TooManyItemsInInClauseException(); } if (first) { first = false; needDelimiter = item is string || item is DateTime || item is Guid; isString = item is string; } else { Sql.Append(','); } if (needDelimiter) { //判断值否是为字符串,如果是查询语句增加N前缀 if (isString) { Sql.Append('N').Append('\'').Append(EscapeSpecialChar(item)).Append('\''); } else { Sql.Append('\'').Append(EscapeSpecialChar(item)).Append('\''); } } else { Sql.Append(item); } } } else if (value is SqlNode) { Sql.AppendLine(); this.Indent++; this.Visit(value as SqlNode); this.Indent--; Sql.AppendLine(); } Sql.Append(')'); return(node); default: break; } return(base.VisitSqlColumnConstraint(node)); }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pkColumn">需要指定主键列</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } //如果是第一页,则只需要使用 TOP 语句即可。 if (pagingInfo.PageNumber == 1) { raw.Top = pagingInfo.PageSize; return raw; } /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * SELECT TOP 10 * * FROM ASN * WHERE ASN.Id > 0 AND ASN.Id NOT IN( * SELECT TOP 20 Id * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * ) * ORDER BY ASN.AsnCode ASC * **********************************************************************/ var excludeSelect = new SqlSelect { Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize, Selection = pkColumn, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy, }; var res = new SqlSelect { Top = pagingInfo.PageSize, Selection = raw.Selection, From = raw.From, OrderBy = raw.OrderBy, }; var newWhere = new SqlColumnConstraint { Column = pkColumn, Operator = SqlColumnConstraintOperator.NotIn, Value = excludeSelect }; if (raw.Where != null) { res.Where = new SqlBinaryConstraint { Left = raw.Where, Opeartor = SqlBinaryConstraintType.And, Right = newWhere }; } else { res.Where = newWhere; } return res; }