public string ToSql() { if (_whereTimes <= 0) { return(null); } var sb = new StringBuilder().Append("DELETE FROM ").Append(_commonUtils.QuoteSqlName(TableRuleInvoke())).Append(" WHERE ").Append(_where); if (_whereGlobalFilter.Any()) { var globalFilterCondi = _commonExpression.GetWhereCascadeSql(new SelectTableInfo { Table = _table }, _whereGlobalFilter.Select(a => a.Where).ToList()); if (string.IsNullOrEmpty(globalFilterCondi) == false) { sb.Append(" AND ").Append(globalFilterCondi); } } return(sb.ToString()); }
internal static string ToSqlStatic(CommonUtils _commonUtils, CommonExpression _commonExpression, string _select, bool _distinct, string field, StringBuilder _join, StringBuilder _where, string _groupby, string _having, string _orderby, int _skip, int _limit, List <SelectTableInfo> _tables, List <Dictionary <Type, string> > tbUnions, List <LambdaExpression> _whereCascadeExpression, IFreeSql _orm) { if (_orm.CodeFirst.IsAutoSyncStructure) { _orm.CodeFirst.SyncStructure(_tables.Select(a => a.Table.Type).ToArray()); } if (_whereCascadeExpression.Any()) { foreach (var tb in _tables.Where(a => a.Type != SelectTableInfoType.Parent)) { tb.Cascade = _commonExpression.GetWhereCascadeSql(tb, _whereCascadeExpression); } } var sb = new StringBuilder(); var tbUnionsGt0 = tbUnions.Count > 1; for (var tbUnionsIdx = 0; tbUnionsIdx < tbUnions.Count; tbUnionsIdx++) { if (tbUnionsIdx > 0) { sb.Append(" \r\n\r\nUNION ALL\r\n\r\n"); } if (tbUnionsGt0) { sb.Append("select * from ("); } var tbUnion = tbUnions[tbUnionsIdx]; var sbnav = new StringBuilder(); sb.Append(_select); if (_distinct) { sb.Append("DISTINCT "); } sb.Append(field); if (string.IsNullOrEmpty(_orderby) && _skip > 0) { sb.Append(", ROWNUM AS \"__rownum__\""); } sb.Append(" \r\nFROM "); var tbsjoin = _tables.Where(a => a.Type != SelectTableInfoType.From).ToArray(); var tbsfrom = _tables.Where(a => a.Type == SelectTableInfoType.From).ToArray(); for (var a = 0; a < tbsfrom.Length; a++) { sb.Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[a].Table.Type])).Append(" ").Append(tbsfrom[a].Alias); if (tbsjoin.Length > 0) { //如果存在 join 查询,则处理 from t1, t2 改为 from t1 inner join t2 on 1 = 1 for (var b = 1; b < tbsfrom.Length; b++) { sb.Append(" \r\nLEFT JOIN ").Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[b].Table.Type])).Append(" ").Append(tbsfrom[b].Alias); if (string.IsNullOrEmpty(tbsfrom[b].NavigateCondition) && string.IsNullOrEmpty(tbsfrom[b].On) && string.IsNullOrEmpty(tbsfrom[b].Cascade)) { sb.Append(" ON 1 = 1"); } else { sb.Append(" ON ").Append(tbsfrom[b].NavigateCondition ?? tbsfrom[b].On); if (string.IsNullOrEmpty(tbsfrom[b].Cascade) == false) { sb.Append(" AND (").Append(tbsfrom[b].Cascade).Append(")"); } } } break; } else { if (!string.IsNullOrEmpty(tbsfrom[a].NavigateCondition)) { sbnav.Append(" AND (").Append(tbsfrom[a].NavigateCondition).Append(")"); } if (!string.IsNullOrEmpty(tbsfrom[a].On)) { sbnav.Append(" AND (").Append(tbsfrom[a].On).Append(")"); } if (a > 0 && !string.IsNullOrEmpty(tbsfrom[a].Cascade)) { sbnav.Append(" AND (").Append(tbsfrom[a].Cascade).Append(")"); } } if (a < tbsfrom.Length - 1) { sb.Append(", "); } } foreach (var tb in tbsjoin) { if (tb.Type == SelectTableInfoType.Parent) { continue; } switch (tb.Type) { case SelectTableInfoType.LeftJoin: sb.Append(" \r\nLEFT JOIN "); break; case SelectTableInfoType.InnerJoin: sb.Append(" \r\nINNER JOIN "); break; case SelectTableInfoType.RightJoin: sb.Append(" \r\nRIGHT JOIN "); break; } sb.Append(_commonUtils.QuoteSqlName(tbUnion[tb.Table.Type])).Append(" ").Append(tb.Alias).Append(" ON ").Append(tb.On ?? tb.NavigateCondition); if (!string.IsNullOrEmpty(tb.Cascade)) { sb.Append(" AND (").Append(tb.Cascade).Append(")"); } if (!string.IsNullOrEmpty(tb.On) && !string.IsNullOrEmpty(tb.NavigateCondition)) { sbnav.Append(" AND (").Append(tb.NavigateCondition).Append(")"); } } if (_join.Length > 0) { sb.Append(_join); } sbnav.Append(_where); if (!string.IsNullOrEmpty(_tables[0].Cascade)) { sbnav.Append(" AND (").Append(_tables[0].Cascade).Append(")"); } foreach (var tb in _tables) { if (tb.Type == SelectTableInfoType.Parent) { continue; } if (string.IsNullOrEmpty(tb.Table.SelectFilter) == false) { sbnav.Append(" AND (").Append(tb.Table.SelectFilter.Replace("a.", $"{tb.Alias}.")).Append(")"); } } if (string.IsNullOrEmpty(_orderby) && (_skip > 0 || _limit > 0)) { sbnav.Append(" AND ROWNUM < ").Append(_skip + _limit + 1); } if (sbnav.Length > 0) { sb.Append(" \r\nWHERE ").Append(sbnav.Remove(0, 5)); } if (string.IsNullOrEmpty(_groupby) == false) { sb.Append(_groupby); if (string.IsNullOrEmpty(_having) == false) { sb.Append(" \r\nHAVING ").Append(_having.Substring(5)); } } sb.Append(_orderby); if (string.IsNullOrEmpty(_orderby)) { if (_skip > 0) { sb.Insert(0, "SELECT t.* FROM (").Append(") t WHERE t.\"__rownum__\" > ").Append(_skip); } } else { if (_skip > 0 && _limit > 0) { sb.Insert(0, "SELECT t.* FROM (SELECT rt.*, ROWNUM AS \"__rownum__\" FROM (").Append(") rt WHERE ROWNUM < ").Append(_skip + _limit + 1).Append(") t WHERE t.\"__rownum__\" > ").Append(_skip); } else if (_skip > 0) { sb.Insert(0, "SELECT t.* FROM (").Append(") t WHERE ROWNUM > ").Append(_skip); } else if (_limit > 0) { sb.Insert(0, "SELECT t.* FROM (").Append(") t WHERE ROWNUM < ").Append(_limit + 1); } } sbnav.Clear(); if (tbUnionsGt0) { sb.Append(") ftb"); } } return(sb.ToString()); }
internal static string ToSqlStatic(CommonUtils _commonUtils, CommonExpression _commonExpression, string _select, bool _distinct, string field, StringBuilder _join, StringBuilder _where, string _groupby, string _having, string _orderby, int _skip, int _limit, List <SelectTableInfo> _tables, List <Dictionary <Type, string> > tbUnions, Func <Type, string, string> _aliasRule, string _tosqlAppendContent, List <GlobalFilter.Item> _whereGlobalFilter, IFreeSql _orm) { if (_orm.CodeFirst.IsAutoSyncStructure) { _orm.CodeFirst.SyncStructure(_tables.Select(a => a.Table.Type).ToArray()); } if (_whereGlobalFilter.Any()) { foreach (var tb in _tables.Where(a => a.Type != SelectTableInfoType.Parent)) { tb.Cascade = _commonExpression.GetWhereCascadeSql(tb, _whereGlobalFilter, true); } } var sb = new StringBuilder(); var tbUnionsGt0 = tbUnions.Count > 1; for (var tbUnionsIdx = 0; tbUnionsIdx < tbUnions.Count; tbUnionsIdx++) { if (tbUnionsIdx > 0) { sb.Append("\r\n \r\nUNION ALL\r\n \r\n"); } if (tbUnionsGt0) { sb.Append(_select).Append(" * from ("); } var tbUnion = tbUnions[tbUnionsIdx]; var sbnav = new StringBuilder(); sb.Append(_select); if (_distinct) { sb.Append("DISTINCT "); } sb.Append(field).Append(" \r\nFROM "); var tbsjoin = _tables.Where(a => a.Type != SelectTableInfoType.From).ToArray(); var tbsfrom = _tables.Where(a => a.Type == SelectTableInfoType.From).ToArray(); for (var a = 0; a < tbsfrom.Length; a++) { sb.Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[a].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[a].Table.Type, tbsfrom[a].Alias) ?? tbsfrom[a].Alias); if (tbsjoin.Length > 0) { //如果存在 join 查询,则处理 from t1, t2 改为 from t1 inner join t2 on 1 = 1 for (var b = 1; b < tbsfrom.Length; b++) { sb.Append(" \r\nLEFT JOIN ").Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[b].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[b].Table.Type, tbsfrom[b].Alias) ?? tbsfrom[b].Alias); if (string.IsNullOrEmpty(tbsfrom[b].NavigateCondition) && string.IsNullOrEmpty(tbsfrom[b].On) && string.IsNullOrEmpty(tbsfrom[b].Cascade)) { sb.Append(" ON 1 = 1"); } else { var onSql = tbsfrom[b].NavigateCondition ?? tbsfrom[b].On; sb.Append(" ON ").Append(onSql); if (string.IsNullOrEmpty(tbsfrom[b].Cascade) == false) { if (string.IsNullOrEmpty(onSql)) { sb.Append(tbsfrom[b].Cascade); } else { sb.Append(" AND ").Append(tbsfrom[b].Cascade); } } } } break; } else { if (!string.IsNullOrEmpty(tbsfrom[a].NavigateCondition)) { sbnav.Append(" AND (").Append(tbsfrom[a].NavigateCondition).Append(")"); } if (!string.IsNullOrEmpty(tbsfrom[a].On)) { sbnav.Append(" AND (").Append(tbsfrom[a].On).Append(")"); } if (a > 0 && !string.IsNullOrEmpty(tbsfrom[a].Cascade)) { sbnav.Append(" AND ").Append(tbsfrom[a].Cascade); } } if (a < tbsfrom.Length - 1) { sb.Append(", "); } } foreach (var tb in tbsjoin) { if (tb.Type == SelectTableInfoType.Parent) { continue; } switch (tb.Type) { case SelectTableInfoType.LeftJoin: sb.Append(" \r\nLEFT JOIN "); break; case SelectTableInfoType.InnerJoin: sb.Append(" \r\nINNER JOIN "); break; case SelectTableInfoType.RightJoin: sb.Append(" \r\nRIGHT JOIN "); break; } sb.Append(_commonUtils.QuoteSqlName(tbUnion[tb.Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tb.Table.Type, tb.Alias) ?? tb.Alias).Append(" ON ").Append(tb.On ?? tb.NavigateCondition); if (!string.IsNullOrEmpty(tb.Cascade)) { sb.Append(" AND ").Append(tb.Cascade); } if (!string.IsNullOrEmpty(tb.On) && !string.IsNullOrEmpty(tb.NavigateCondition)) { sbnav.Append(" AND (").Append(tb.NavigateCondition).Append(")"); } } if (_join.Length > 0) { sb.Append(_join); } sbnav.Append(_where); if (!string.IsNullOrEmpty(_tables[0].Cascade)) { sbnav.Append(" AND ").Append(_tables[0].Cascade); } if (sbnav.Length > 0) { sb.Append(" \r\nWHERE ").Append(sbnav.Remove(0, 5)); } if (string.IsNullOrEmpty(_groupby) == false) { sb.Append(_groupby); if (string.IsNullOrEmpty(_having) == false) { sb.Append(" \r\nHAVING ").Append(_having.Substring(5)); } } sb.Append(_orderby); if (_limit > 0) { sb.Append(" \r\nlimit ").Append(_limit); } if (_skip > 0) { sb.Append(" \r\noffset ").Append(_skip); } sbnav.Clear(); if (tbUnionsGt0) { sb.Append(") ftb"); } } return(sb.Append(_tosqlAppendContent).ToString()); }
public string ToSql() { if (_where.Length == 0 && _source.Any() == false) { return(null); } var sb = new StringBuilder(); sb.Append("UPDATE ").Append(_commonUtils.QuoteSqlName(TableRuleInvoke())).Append(" SET "); if (_set.Length > 0) { //指定 set 更新 sb.Append(_set.ToString().Substring(2)); } else if (_source.Count == 1) { //保存 Source _paramsSource.Clear(); var colidx = 0; foreach (var col in _table.Columns.Values) { if (col.Attribute.IsIdentity == false && col.Attribute.IsVersion == false && _ignore.ContainsKey(col.Attribute.Name) == false) { if (colidx > 0) { sb.Append(", "); } sb.Append(_commonUtils.QuoteSqlName(col.Attribute.Name)).Append(" = "); if (col.Attribute.CanUpdate && string.IsNullOrEmpty(col.DbUpdateValue) == false) { sb.Append(col.DbUpdateValue); } else { var val = col.GetMapValue(_source.First()); if (_noneParameter) { sb.Append(_commonUtils.GetNoneParamaterSqlValue(_paramsSource, col.Attribute.MapType, val)); } else { sb.Append(_commonUtils.QuoteWriteParamter(col.Attribute.MapType, _commonUtils.QuoteParamterName($"p_{_paramsSource.Count}"))); _commonUtils.AppendParamter(_paramsSource, null, col, col.Attribute.MapType, val); } } ++colidx; } } if (colidx == 0) { return(null); } } else if (_source.Count > 1) { //批量保存 Source if (_table.Primarys.Any() == false) { return(null); } var caseWhen = new StringBuilder(); caseWhen.Append("CASE "); ToSqlCase(caseWhen, _table.Primarys); var cw = caseWhen.ToString(); _paramsSource.Clear(); var colidx = 0; foreach (var col in _table.Columns.Values) { if (col.Attribute.IsIdentity == false && col.Attribute.IsVersion == false && _ignore.ContainsKey(col.Attribute.Name) == false) { if (colidx > 0) { sb.Append(", "); } sb.Append(_commonUtils.QuoteSqlName(col.Attribute.Name)).Append(" = "); if (col.Attribute.CanUpdate && string.IsNullOrEmpty(col.DbUpdateValue) == false) { sb.Append(col.DbUpdateValue); } else { var nulls = 0; var cwsb = new StringBuilder().Append(cw); foreach (var d in _source) { cwsb.Append(" \r\nWHEN "); ToSqlWhen(cwsb, _table.Primarys, d); cwsb.Append(" THEN "); var val = col.GetMapValue(d); if (_noneParameter) { cwsb.Append(_commonUtils.GetNoneParamaterSqlValue(_paramsSource, col.Attribute.MapType, val)); } else { cwsb.Append(_commonUtils.QuoteWriteParamter(col.Attribute.MapType, _commonUtils.QuoteParamterName($"p_{_paramsSource.Count}"))); _commonUtils.AppendParamter(_paramsSource, null, col, col.Attribute.MapType, val); } if (val == null || val == DBNull.Value) { nulls++; } } cwsb.Append(" END"); if (nulls == _source.Count) { sb.Append("NULL"); } else { ToSqlCaseWhenEnd(cwsb, col); sb.Append(cwsb.ToString()); } cwsb.Clear(); } ++colidx; } } if (colidx == 0) { return(null); } } else if (_setIncr.Length == 0) { return(null); } if (_setIncr.Length > 0) { sb.Append(_set.Length > 0 ? _setIncr.ToString() : _setIncr.ToString().Substring(2)); } if (_source.Any() == false) { foreach (var col in _table.Columns.Values) { if (col.Attribute.CanUpdate && string.IsNullOrEmpty(col.DbUpdateValue) == false) { sb.Append(", ").Append(_commonUtils.QuoteSqlName(col.Attribute.Name)).Append(" = ").Append(col.DbUpdateValue); } } } if (_table.VersionColumn != null) { var vcname = _commonUtils.QuoteSqlName(_table.VersionColumn.Attribute.Name); sb.Append(", ").Append(vcname).Append(" = ").Append(_commonUtils.IsNull(vcname, 0)).Append(" + 1"); } sb.Append(" \r\nWHERE "); if (_source.Any()) { sb.Append("(").Append(_commonUtils.WhereItems(_table, "", _source)).Append(")"); } if (_where.Length > 0) { sb.Append(_source.Any() ? _where.ToString() : _where.ToString().Substring(5)); } if (_whereGlobalFilter.Any()) { var globalFilterCondi = _commonExpression.GetWhereCascadeSql(new SelectTableInfo { Table = _table }, _whereGlobalFilter.Select(a => a.Where).ToList()); if (string.IsNullOrEmpty(globalFilterCondi) == false) { sb.Append(" AND ").Append(globalFilterCondi); } } if (_table.VersionColumn != null) { var versionCondi = WhereCaseSource(_table.VersionColumn.CsName, sqlval => sqlval); if (string.IsNullOrEmpty(versionCondi) == false) { sb.Append(" AND ").Append(versionCondi); } } return(sb.ToString()); }
public string ToSql() { if (_where.Length == 0 && _source.Any() == false) { return(null); } var sb = new StringBuilder(); sb.Append("UPDATE ").Append(_commonUtils.QuoteSqlName(TableRuleInvoke())).Append(" SET "); if (_set.Length > 0) { //指定 set 更新 sb.Append(_set.ToString().Substring(2)); } else if (_source.Count == 1) { //保存 Source _paramsSource.Clear(); var colidx = 0; foreach (var col in _table.Columns.Values) { if (col.Attribute.IsPrimary) { continue; } if (_tempPrimarys.Any(a => a.CsName == col.CsName)) { continue; } if (col.Attribute.IsIdentity == false && col.Attribute.IsVersion == false && _ignore.ContainsKey(col.Attribute.Name) == false) { if (colidx > 0) { sb.Append(", "); } sb.Append(_commonUtils.QuoteSqlName(col.Attribute.Name)).Append(" = "); if (col.Attribute.CanUpdate && string.IsNullOrEmpty(col.DbUpdateValue) == false) { sb.Append(col.DbUpdateValue); } else { var val = col.GetDbValue(_source.First()); var colsql = _noneParameter ? _commonUtils.GetNoneParamaterSqlValue(_paramsSource, "u", col, col.Attribute.MapType, val) : _commonUtils.QuoteWriteParamterAdapter(col.Attribute.MapType, _commonUtils.QuoteParamterName($"p_{_paramsSource.Count}")); sb.Append(_commonUtils.RewriteColumn(col, colsql)); if (_noneParameter == false) { _commonUtils.AppendParamter(_paramsSource, null, col, col.Attribute.MapType, val); } } ++colidx; } } if (colidx == 0) { return(null); } } else if (_source.Count > 1) { //批量保存 Source if (_tempPrimarys.Any() == false) { return(null); } var caseWhen = new StringBuilder(); caseWhen.Append("CASE "); ToSqlCase(caseWhen, _tempPrimarys); var cw = caseWhen.ToString(); _paramsSource.Clear(); var colidx = 0; foreach (var col in _table.Columns.Values) { if (col.Attribute.IsPrimary) { continue; } if (_tempPrimarys.Any(a => a.CsName == col.CsName)) { continue; } if (col.Attribute.IsIdentity == false && col.Attribute.IsVersion == false && _ignore.ContainsKey(col.Attribute.Name) == false) { if (colidx > 0) { sb.Append(", "); } sb.Append(_commonUtils.QuoteSqlName(col.Attribute.Name)).Append(" = "); if (col.Attribute.CanUpdate && string.IsNullOrEmpty(col.DbUpdateValue) == false) { sb.Append(col.DbUpdateValue); } else { var nulls = 0; var cwsb = new StringBuilder().Append(cw); foreach (var d in _source) { cwsb.Append(" \r\nWHEN "); ToSqlWhen(cwsb, _tempPrimarys, d); cwsb.Append(" THEN "); var val = col.GetDbValue(d); var colsql = _noneParameter ? _commonUtils.GetNoneParamaterSqlValue(_paramsSource, "u", col, col.Attribute.MapType, val) : _commonUtils.QuoteWriteParamterAdapter(col.Attribute.MapType, _commonUtils.QuoteParamterName($"p_{_paramsSource.Count}")); cwsb.Append(_commonUtils.RewriteColumn(col, colsql)); if (_noneParameter == false) { _commonUtils.AppendParamter(_paramsSource, null, col, col.Attribute.MapType, val); } if (val == null || val == DBNull.Value) { nulls++; } } cwsb.Append(" END"); if (nulls == _source.Count) { sb.Append("NULL"); } else { ToSqlCaseWhenEnd(cwsb, col); sb.Append(cwsb); } cwsb.Clear(); } ++colidx; } } if (colidx == 0) { return(null); } } else if (_setIncr.Length == 0) { return(null); } if (_setIncr.Length > 0) { sb.Append(_set.Length > 0 ? _setIncr.ToString() : _setIncr.ToString().Substring(2)); } if (_source.Any() == false) { foreach (var col in _table.Columns.Values) { if (col.Attribute.CanUpdate && string.IsNullOrEmpty(col.DbUpdateValue) == false) { sb.Append(", ").Append(_commonUtils.QuoteSqlName(col.Attribute.Name)).Append(" = ").Append(col.DbUpdateValue); } } } if (_table.VersionColumn != null) { var vcname = _commonUtils.QuoteSqlName(_table.VersionColumn.Attribute.Name); if (_table.VersionColumn.Attribute.MapType == typeof(byte[])) { _updateVersionValue = Utils.GuidToBytes(Guid.NewGuid()); sb.Append(", ").Append(vcname).Append(" = ").Append(_commonUtils.GetNoneParamaterSqlValue(_paramsSource, "uv", _table.VersionColumn, _table.VersionColumn.Attribute.MapType, _updateVersionValue)); } else { sb.Append(", ").Append(vcname).Append(" = ").Append(_commonUtils.IsNull(vcname, 0)).Append(" + 1"); } } sb.Append(" \r\nWHERE "); if (_source.Any()) { if (_tempPrimarys.Any() == false) { throw new ArgumentException($"{_table.Type.DisplayCsharp()} 没有定义主键,无法使用 SetSource,请尝试 SetDto"); } sb.Append('(').Append(_commonUtils.WhereItems(_tempPrimarys, "", _source)).Append(')'); } if (_where.Length > 0) { sb.Append(_source.Any() ? _where.ToString() : _where.ToString().Substring(5)); } if (_whereGlobalFilter.Any()) { var globalFilterCondi = _commonExpression.GetWhereCascadeSql(new SelectTableInfo { Table = _table }, _whereGlobalFilter, false); if (string.IsNullOrEmpty(globalFilterCondi) == false) { sb.Append(" AND ").Append(globalFilterCondi); } } if (_table.VersionColumn != null) { var versionCondi = WhereCaseSource(_table.VersionColumn.CsName, sqlval => sqlval); if (string.IsNullOrEmpty(versionCondi) == false) { sb.Append(" AND ").Append(versionCondi); } } _interceptSql?.Invoke(sb); return(sb.ToString()); }
internal static string ToSqlStatic(CommonUtils _commonUtils, CommonExpression _commonExpression, string _select, bool _distinct, string field, StringBuilder _join, StringBuilder _where, string _groupby, string _having, string _orderby, int _skip, int _limit, List <SelectTableInfo> _tables, List <Dictionary <Type, string> > tbUnions, List <LambdaExpression> _whereCascadeExpression, IFreeSql _orm) { var _utils = _commonUtils as OdbcUtils; if (_orm.CodeFirst.IsAutoSyncStructure) { _orm.CodeFirst.SyncStructure(_tables.Select(a => a.Table.Type).ToArray()); } if (_whereCascadeExpression.Any()) { foreach (var tb in _tables.Where(a => a.Type != SelectTableInfoType.Parent)) { tb.Cascade = _commonExpression.GetWhereCascadeSql(tb, _whereCascadeExpression); } } var sb = new StringBuilder(); var tbUnionsGt0 = tbUnions.Count > 1; for (var tbUnionsIdx = 0; tbUnionsIdx < tbUnions.Count; tbUnionsIdx++) { if (tbUnionsIdx > 0) { sb.Append(" \r\n\r\nUNION ALL\r\n\r\n"); } if (tbUnionsGt0) { sb.Append("select * from ("); } var tbUnion = tbUnions[tbUnionsIdx]; var sbnav = new StringBuilder(); sb.Append(_select); if (_distinct) { sb.Append("DISTINCT "); } if (_limit > 0 && _utils.Adapter.SelectTopStyle == OdbcAdapter.SelecTopStyle.Top) { sb.Append("TOP ").Append(_skip + _limit).Append(" "); } sb.Append(field); if (_skip > 0) { if (string.IsNullOrEmpty(_orderby)) { var pktb = _tables.Where(a => a.Table.Primarys.Any()).FirstOrDefault(); if (pktb != null) { _orderby = string.Concat(" \r\nORDER BY ", pktb.Alias, ".", _commonUtils.QuoteSqlName(pktb?.Table.Primarys.First().Attribute.Name)); } else { _orderby = string.Concat(" \r\nORDER BY ", _tables.First().Alias, ".", _commonUtils.QuoteSqlName(_tables.First().Table.Columns.First().Value.Attribute.Name)); } } sb.Append(", ROW_NUMBER() OVER(").Append(_orderby).Append(") AS __rownum__"); throw new NotImplementedException("FreeSql.Odbc.Default 未实现 Skip/Offset 功能,如果需要分页请使用判断上一次 id"); } sb.Append(" \r\nFROM "); var tbsjoin = _tables.Where(a => a.Type != SelectTableInfoType.From).ToArray(); var tbsfrom = _tables.Where(a => a.Type == SelectTableInfoType.From).ToArray(); for (var a = 0; a < tbsfrom.Length; a++) { sb.Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[a].Table.Type])).Append(" ").Append(tbsfrom[a].Alias); if (tbsjoin.Length > 0) { //如果存在 join 查询,则处理 from t1, t2 改为 from t1 inner join t2 on 1 = 1 for (var b = 1; b < tbsfrom.Length; b++) { sb.Append(" \r\nLEFT JOIN ").Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[b].Table.Type])).Append(" ").Append(tbsfrom[b].Alias); if (string.IsNullOrEmpty(tbsfrom[b].NavigateCondition) && string.IsNullOrEmpty(tbsfrom[b].On) && string.IsNullOrEmpty(tbsfrom[b].Cascade)) { sb.Append(" ON 1 = 1"); } else { sb.Append(" ON ").Append(tbsfrom[b].NavigateCondition ?? tbsfrom[b].On); if (string.IsNullOrEmpty(tbsfrom[b].Cascade) == false) { sb.Append(" AND (").Append(tbsfrom[b].Cascade).Append(")"); } } } break; } else { if (!string.IsNullOrEmpty(tbsfrom[a].NavigateCondition)) { sbnav.Append(" AND (").Append(tbsfrom[a].NavigateCondition).Append(")"); } if (!string.IsNullOrEmpty(tbsfrom[a].On)) { sbnav.Append(" AND (").Append(tbsfrom[a].On).Append(")"); } if (a > 0 && !string.IsNullOrEmpty(tbsfrom[a].Cascade)) { sbnav.Append(" AND (").Append(tbsfrom[a].Cascade).Append(")"); } } if (a < tbsfrom.Length - 1) { sb.Append(", "); } } foreach (var tb in tbsjoin) { if (tb.Type == SelectTableInfoType.Parent) { continue; } switch (tb.Type) { case SelectTableInfoType.LeftJoin: sb.Append(" \r\nLEFT JOIN "); break; case SelectTableInfoType.InnerJoin: sb.Append(" \r\nINNER JOIN "); break; case SelectTableInfoType.RightJoin: sb.Append(" \r\nRIGHT JOIN "); break; } sb.Append(_commonUtils.QuoteSqlName(tbUnion[tb.Table.Type])).Append(" ").Append(tb.Alias).Append(" ON ").Append(tb.On ?? tb.NavigateCondition); if (!string.IsNullOrEmpty(tb.Cascade)) { sb.Append(" AND (").Append(tb.Cascade).Append(")"); } if (!string.IsNullOrEmpty(tb.On) && !string.IsNullOrEmpty(tb.NavigateCondition)) { sbnav.Append(" AND (").Append(tb.NavigateCondition).Append(")"); } } if (_join.Length > 0) { sb.Append(_join); } sbnav.Append(_where); if (!string.IsNullOrEmpty(_tables[0].Cascade)) { sbnav.Append(" AND (").Append(_tables[0].Cascade).Append(")"); } foreach (var tb in _tables) { if (tb.Type == SelectTableInfoType.Parent) { continue; } if (string.IsNullOrEmpty(tb.Table.SelectFilter) == false) { sbnav.Append(" AND (").Append(tb.Table.SelectFilter.Replace("a.", $"{tb.Alias}.")).Append(")"); } } if (sbnav.Length > 0) { sb.Append(" \r\nWHERE ").Append(sbnav.Remove(0, 5)); } if (string.IsNullOrEmpty(_groupby) == false) { sb.Append(_groupby); if (string.IsNullOrEmpty(_having) == false) { sb.Append(" \r\nHAVING ").Append(_having.Substring(5)); } } if (_skip <= 0) { sb.Append(_orderby); } else { sb.Insert(0, "WITH t AS ( ").Append(" ) SELECT t.* FROM t where __rownum__ > ").Append(_skip); } sbnav.Clear(); if (_limit > 0 && _utils.Adapter.SelectTopStyle == OdbcAdapter.SelecTopStyle.Limit) { sb.Append(" \r\nLIMIT ").Append(_skip + _limit); } if (tbUnionsGt0) { sb.Append(") ftb"); } } return(sb.ToString()); }
internal static string ToSqlStatic(CommonUtils _commonUtils, CommonExpression _commonExpression, string _select, bool _distinct, string field, StringBuilder _join, StringBuilder _where, string _groupby, string _having, string _orderby, int _skip, int _limit, List <SelectTableInfo> _tables, List <Dictionary <Type, string> > tbUnions, Func <Type, string, string> _aliasRule, string _tosqlAppendContent, List <LambdaExpression> _whereCascadeExpression, IFreeSql _orm) { if (_orm.CodeFirst.IsAutoSyncStructure) { _orm.CodeFirst.SyncStructure(_tables.Select(a => a.Table.Type).ToArray()); } if (_whereCascadeExpression.Any()) { foreach (var tb in _tables.Where(a => a.Type != SelectTableInfoType.Parent)) { tb.Cascade = _commonExpression.GetWhereCascadeSql(tb, _whereCascadeExpression, true); } } var sb = new StringBuilder(); var tbUnionsGt0 = tbUnions.Count > 1; for (var tbUnionsIdx = 0; tbUnionsIdx < tbUnions.Count; tbUnionsIdx++) { if (tbUnionsIdx > 0) { sb.Append(" \r\n\r\nUNION ALL\r\n\r\n"); } if (tbUnionsGt0) { sb.Append(_select).Append(" * from ("); } var tbUnion = tbUnions[tbUnionsIdx]; var sbnav = new StringBuilder(); sb.Append(_select); if (_distinct) { sb.Append("DISTINCT "); } if (_limit > 0) { sb.Append("TOP ").Append(_skip + _limit).Append(" "); } sb.Append(field); if (_skip > 0) { throw new NotImplementedException("FreeSql.Provider.MsAccess 未实现 Skip/Offset 功能,如果需要分页请使用判断上一次 id"); } sb.Append(" \r\nFROM "); var fromIndex = sb.Length; var ioinCounter = 0; var tbsjoin = _tables.Where(a => a.Type != SelectTableInfoType.From).ToArray(); var tbsfrom = _tables.Where(a => a.Type == SelectTableInfoType.From).ToArray(); for (var a = 0; a < tbsfrom.Length; a++) { sb.Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[a].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[a].Table.Type, tbsfrom[a].Alias) ?? tbsfrom[a].Alias); if (tbsjoin.Length > 0) { //如果存在 join 查询,则处理 from t1, t2 改为 from t1 inner join t2 on 1 = 1 for (var b = 1; b < tbsfrom.Length; b++) { if (ioinCounter++ > 0) { sb.Insert(fromIndex, "(").Append(") "); } sb.Append(" \r\nLEFT JOIN ").Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[b].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[b].Table.Type, tbsfrom[b].Alias) ?? tbsfrom[b].Alias); if (string.IsNullOrEmpty(tbsfrom[b].NavigateCondition) && string.IsNullOrEmpty(tbsfrom[b].On) && string.IsNullOrEmpty(tbsfrom[b].Cascade)) { sb.Append(" ON 1 = 1"); } else { var onSql = tbsfrom[b].NavigateCondition ?? tbsfrom[b].On; sb.Append(" ON (").Append(onSql); if (string.IsNullOrEmpty(tbsfrom[b].Cascade) == false) { if (string.IsNullOrEmpty(onSql)) { sb.Append(tbsfrom[b].Cascade); } else { sb.Append(" AND (").Append(tbsfrom[b].Cascade).Append(")"); } } sb.Append(")"); } } break; } else { if (!string.IsNullOrEmpty(tbsfrom[a].NavigateCondition)) { sbnav.Append(" AND (").Append(tbsfrom[a].NavigateCondition).Append(")"); } if (!string.IsNullOrEmpty(tbsfrom[a].On)) { sbnav.Append(" AND (").Append(tbsfrom[a].On).Append(")"); } if (a > 0 && !string.IsNullOrEmpty(tbsfrom[a].Cascade)) { sbnav.Append(" AND (").Append(tbsfrom[a].Cascade).Append(")"); } } if (a < tbsfrom.Length - 1) { sb.Append(", "); } } foreach (var tb in tbsjoin) { if (tb.Type == SelectTableInfoType.Parent) { continue; } switch (tb.Type) { case SelectTableInfoType.LeftJoin: if (ioinCounter++ > 0) { sb.Insert(fromIndex, "(").Append(") "); } sb.Append(" \r\nLEFT JOIN "); break; case SelectTableInfoType.InnerJoin: if (ioinCounter++ > 0) { sb.Insert(fromIndex, "(").Append(") "); } sb.Append(" \r\nINNER JOIN "); break; case SelectTableInfoType.RightJoin: if (ioinCounter++ > 0) { sb.Insert(fromIndex, "(").Append(") "); } sb.Append(" \r\nRIGHT JOIN "); break; } sb.Append(_commonUtils.QuoteSqlName(tbUnion[tb.Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tb.Table.Type, tb.Alias) ?? tb.Alias).Append(" ON (").Append(tb.On ?? tb.NavigateCondition); if (!string.IsNullOrEmpty(tb.Cascade)) { sb.Append(" AND (").Append(tb.Cascade).Append(")"); } if (!string.IsNullOrEmpty(tb.On) && !string.IsNullOrEmpty(tb.NavigateCondition)) { sbnav.Append(" AND (").Append(tb.NavigateCondition).Append(")"); } sb.Append(")"); } if (_join.Length > 0) { sb.Append(Regex.Replace(_join.ToString(), @" \r\n(LEFT|INNER|RIGHT) JOIN ", m => { if (ioinCounter++ > 0) { sb.Insert(fromIndex, "(").Append(") "); return($") {m.Groups[0].Value}"); } return(m.Groups[0].Value); }, RegexOptions.IgnoreCase)); } sbnav.Append(_where); if (!string.IsNullOrEmpty(_tables[0].Cascade)) { sbnav.Append(" AND (").Append(_tables[0].Cascade).Append(")"); } if (sbnav.Length > 0) { sb.Append(" \r\nWHERE ").Append(sbnav.Remove(0, 5)); } if (string.IsNullOrEmpty(_groupby) == false) { sb.Append(_groupby); if (string.IsNullOrEmpty(_having) == false) { sb.Append(" \r\nHAVING ").Append(_having.Substring(5)); } } sb.Append(_orderby); sbnav.Clear(); if (tbUnionsGt0) { sb.Append(") ftb"); } } return(sb.Append(_tosqlAppendContent).ToString()); }
internal static string ToSqlStaticRowNumber(CommonUtils _commonUtils, CommonExpression _commonExpression, string _select, bool _distinct, string field, StringBuilder _join, StringBuilder _where, string _groupby, string _having, string _orderby, int _skip, int _limit, List <SelectTableInfo> _tables, List <Dictionary <Type, string> > tbUnions, Func <Type, string, string> _aliasRule, string _tosqlAppendContent, List <GlobalFilter.Item> _whereGlobalFilter, IFreeSql _orm) { if (_orm.CodeFirst.IsAutoSyncStructure) { _orm.CodeFirst.SyncStructure(_tables.Select(a => a.Table.Type).ToArray()); } if (_whereGlobalFilter.Any()) { foreach (var tb in _tables.Where(a => a.Type != SelectTableInfoType.Parent)) { tb.Cascade = _commonExpression.GetWhereCascadeSql(tb, _whereGlobalFilter, true); } } var sb = new StringBuilder(); var tbUnionsGt0 = tbUnions.Count > 1; for (var tbUnionsIdx = 0; tbUnionsIdx < tbUnions.Count; tbUnionsIdx++) { if (tbUnionsIdx > 0) { sb.Append("\r\n \r\nUNION ALL\r\n \r\n"); } if (tbUnionsGt0) { sb.Append(_select).Append(" * from ("); } var tbUnion = tbUnions[tbUnionsIdx]; var sbnav = new StringBuilder(); sb.Append(_select); if (_distinct) { sb.Append("DISTINCT "); } //if (_limit > 0) sb.Append("TOP ").Append(_skip + _limit).Append(" "); //TOP 会引发 __rownum__ 无序的问题 if (_skip <= 0 && _limit > 0) { sb.Append("TOP ").Append(_limit).Append(" "); } sb.Append(field); if (_limit > 0) { if (string.IsNullOrEmpty(_orderby)) { var pktb = _tables.Where(a => a.Table.Primarys.Any()).FirstOrDefault(); if (pktb != null) { _orderby = string.Concat(" \r\nORDER BY ", pktb.Alias, ".", _commonUtils.QuoteSqlName(pktb?.Table.Primarys.First().Attribute.Name)); } else { _orderby = string.Concat(" \r\nORDER BY ", _tables.First().Alias, ".", _commonUtils.QuoteSqlName(_tables.First().Table.Columns.First().Value.Attribute.Name)); } } if (_skip > 0) // 注意这个判断,大于 0 才使用 ROW_NUMBER ,否则属于第一页直接使用 TOP { sb.Append(", ROW_NUMBER() OVER(").Append(_orderby).Append(") AS __rownum__"); } } sb.Append(" \r\nFROM "); var tbsjoin = _tables.Where(a => a.Type != SelectTableInfoType.From).ToArray(); var tbsfrom = _tables.Where(a => a.Type == SelectTableInfoType.From).ToArray(); for (var a = 0; a < tbsfrom.Length; a++) { sb.Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[a].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[a].Table.Type, tbsfrom[a].Alias) ?? tbsfrom[a].Alias); if (tbsjoin.Length > 0) { //如果存在 join 查询,则处理 from t1, t2 改为 from t1 inner join t2 on 1 = 1 for (var b = 1; b < tbsfrom.Length; b++) { sb.Append(" \r\nLEFT JOIN ").Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[b].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[b].Table.Type, tbsfrom[b].Alias) ?? tbsfrom[b].Alias); if (string.IsNullOrEmpty(tbsfrom[b].NavigateCondition) && string.IsNullOrEmpty(tbsfrom[b].On) && string.IsNullOrEmpty(tbsfrom[b].Cascade)) { sb.Append(" ON 1 = 1"); } else { var onSql = tbsfrom[b].NavigateCondition ?? tbsfrom[b].On; sb.Append(" ON ").Append(onSql); if (string.IsNullOrEmpty(tbsfrom[b].Cascade) == false) { if (string.IsNullOrEmpty(onSql)) { sb.Append(tbsfrom[b].Cascade); } else { sb.Append(" AND ").Append(tbsfrom[b].Cascade); } } } } break; } else { if (!string.IsNullOrEmpty(tbsfrom[a].NavigateCondition)) { sbnav.Append(" AND (").Append(tbsfrom[a].NavigateCondition).Append(")"); } if (!string.IsNullOrEmpty(tbsfrom[a].On)) { sbnav.Append(" AND (").Append(tbsfrom[a].On).Append(")"); } if (a > 0 && !string.IsNullOrEmpty(tbsfrom[a].Cascade)) { sbnav.Append(" AND ").Append(tbsfrom[a].Cascade); } } if (a < tbsfrom.Length - 1) { sb.Append(", "); } } foreach (var tb in tbsjoin) { switch (tb.Type) { case SelectTableInfoType.Parent: case SelectTableInfoType.RawJoin: continue; case SelectTableInfoType.LeftJoin: sb.Append(" \r\nLEFT JOIN "); break; case SelectTableInfoType.InnerJoin: sb.Append(" \r\nINNER JOIN "); break; case SelectTableInfoType.RightJoin: sb.Append(" \r\nRIGHT JOIN "); break; } sb.Append(_commonUtils.QuoteSqlName(tbUnion[tb.Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tb.Table.Type, tb.Alias) ?? tb.Alias).Append(" ON ").Append(tb.On ?? tb.NavigateCondition); if (!string.IsNullOrEmpty(tb.Cascade)) { sb.Append(" AND ").Append(tb.Cascade); } if (!string.IsNullOrEmpty(tb.On) && !string.IsNullOrEmpty(tb.NavigateCondition)) { sbnav.Append(" AND (").Append(tb.NavigateCondition).Append(")"); } } if (_join.Length > 0) { sb.Append(_join); } sbnav.Append(_where); if (!string.IsNullOrEmpty(_tables[0].Cascade)) { sbnav.Append(" AND ").Append(_tables[0].Cascade); } if (sbnav.Length > 0) { sb.Append(" \r\nWHERE ").Append(sbnav.Remove(0, 5)); } if (string.IsNullOrEmpty(_groupby) == false) { sb.Append(_groupby); if (string.IsNullOrEmpty(_having) == false) { sb.Append(" \r\nHAVING ").Append(_having.Substring(5)); } } if (_skip <= 0) { sb.Append(_orderby); } else { sb.Insert(0, "WITH t AS ( ").Append(" ) SELECT t.* FROM t where __rownum__ between ").Append(_skip + 1).Append(" and ").Append(_skip + _limit); } sbnav.Clear(); if (tbUnionsGt0) { sb.Append(") ftb"); } } return(sb.Append(_tosqlAppendContent).ToString()); }
internal static string ToSqlStatic(CommonUtils _commonUtils, CommonExpression _commonExpression, string _select, bool _distinct, string field, StringBuilder _join, StringBuilder _where, string _groupby, string _having, string _orderby, int _skip, int _limit, List <SelectTableInfo> _tables, Func <Type, string, string> tableRuleInvoke, List <LambdaExpression> _whereCascadeExpression, IFreeSql _orm) { if (_orm.CodeFirst.IsAutoSyncStructure) { _orm.CodeFirst.SyncStructure(_tables.Select(a => a.Table.Type).ToArray()); } if (_whereCascadeExpression.Any()) { foreach (var tb in _tables.Where(a => a.Type != SelectTableInfoType.Parent)) { tb.Cascade = _commonExpression.GetWhereCascadeSql(tb, _whereCascadeExpression); } } var sb = new StringBuilder(); var sbnav = new StringBuilder(); sb.Append(_select); if (_distinct) { sb.Append("DISTINCT "); } sb.Append(field).Append(" \r\nFROM "); var tbsjoin = _tables.Where(a => a.Type != SelectTableInfoType.From).ToArray(); var tbsfrom = _tables.Where(a => a.Type == SelectTableInfoType.From).ToArray(); for (var a = 0; a < tbsfrom.Length; a++) { sb.Append(_commonUtils.QuoteSqlName(tableRuleInvoke(tbsfrom[a].Table.Type, tbsfrom[a].Table.DbName))).Append(" ").Append(tbsfrom[a].Alias); if (tbsjoin.Length > 0) { //如果存在 join 查询,则处理 from t1, t2 改为 from t1 inner join t2 on 1 = 1 for (var b = 1; b < tbsfrom.Length; b++) { sb.Append(" \r\nLEFT JOIN ").Append(_commonUtils.QuoteSqlName(tableRuleInvoke(tbsfrom[b].Table.Type, tbsfrom[b].Table.DbName))).Append(" ").Append(tbsfrom[b].Alias); if (string.IsNullOrEmpty(tbsfrom[b].NavigateCondition) && string.IsNullOrEmpty(tbsfrom[b].On) && string.IsNullOrEmpty(tbsfrom[b].Cascade)) { sb.Append(" ON 1 = 1"); } else { sb.Append(" ON ").Append(tbsfrom[b].NavigateCondition ?? tbsfrom[b].On); if (string.IsNullOrEmpty(tbsfrom[b].Cascade) == false) { sb.Append(" AND (").Append(tbsfrom[b].Cascade).Append(")"); } } } break; } else { if (!string.IsNullOrEmpty(tbsfrom[a].NavigateCondition)) { sbnav.Append(" AND (").Append(tbsfrom[a].NavigateCondition).Append(")"); } if (!string.IsNullOrEmpty(tbsfrom[a].On)) { sbnav.Append(" AND (").Append(tbsfrom[a].On).Append(")"); } if (a > 0 && !string.IsNullOrEmpty(tbsfrom[a].Cascade)) { sbnav.Append(" AND (").Append(tbsfrom[a].Cascade).Append(")"); } } if (a < tbsfrom.Length - 1) { sb.Append(", "); } } foreach (var tb in tbsjoin) { if (tb.Type == SelectTableInfoType.Parent) { continue; } switch (tb.Type) { case SelectTableInfoType.LeftJoin: sb.Append(" \r\nLEFT JOIN "); break; case SelectTableInfoType.InnerJoin: sb.Append(" \r\nINNER JOIN "); break; case SelectTableInfoType.RightJoin: sb.Append(" \r\nRIGHT JOIN "); break; } sb.Append(_commonUtils.QuoteSqlName(tableRuleInvoke(tb.Table.Type, tb.Table.DbName))).Append(" ").Append(tb.Alias).Append(" ON ").Append(tb.On ?? tb.NavigateCondition); if (!string.IsNullOrEmpty(tb.Cascade)) { sb.Append(" AND (").Append(tb.Cascade).Append(")"); } if (!string.IsNullOrEmpty(tb.On) && !string.IsNullOrEmpty(tb.NavigateCondition)) { sbnav.Append(" AND (").Append(tb.NavigateCondition).Append(")"); } } if (_join.Length > 0) { sb.Append(_join); } sbnav.Append(_where); if (!string.IsNullOrEmpty(_tables[0].Cascade)) { sbnav.Append(" AND (").Append(_tables[0].Cascade).Append(")"); } foreach (var tb in _tables) { if (tb.Type == SelectTableInfoType.Parent) { continue; } if (string.IsNullOrEmpty(tb.Table.SelectFilter) == false) { sbnav.Append(" AND (").Append(tb.Table.SelectFilter.Replace("a.", $"{tb.Alias}.")).Append(")"); } } if (sbnav.Length > 0) { sb.Append(" \r\nWHERE ").Append(sbnav.Remove(0, 5)); } if (string.IsNullOrEmpty(_groupby) == false) { sb.Append(_groupby); if (string.IsNullOrEmpty(_having) == false) { sb.Append(" \r\nHAVING ").Append(_having.Substring(5)); } } sb.Append(_orderby); if (_skip > 0 || _limit > 0) { sb.Append(" \r\nlimit ").Append(Math.Max(0, _skip)).Append(",").Append(_limit > 0 ? _limit : -1); } sbnav.Clear(); return(sb.ToString()); }
internal static string ToSqlStaticOffsetFetchNext(CommonUtils _commonUtils, CommonExpression _commonExpression, string _select, bool _distinct, string field, StringBuilder _join, StringBuilder _where, string _groupby, string _having, string _orderby, int _skip, int _limit, List <SelectTableInfo> _tables, List <Dictionary <Type, string> > tbUnions, Func <Type, string, string> _aliasRule, List <LambdaExpression> _whereCascadeExpression, IFreeSql _orm) { if (_orm.CodeFirst.IsAutoSyncStructure) { _orm.CodeFirst.SyncStructure(_tables.Select(a => a.Table.Type).ToArray()); } if (_whereCascadeExpression.Any()) { foreach (var tb in _tables.Where(a => a.Type != SelectTableInfoType.Parent)) { tb.Cascade = _commonExpression.GetWhereCascadeSql(tb, _whereCascadeExpression); } } var sb = new StringBuilder(); var tbUnionsGt0 = tbUnions.Count > 1; for (var tbUnionsIdx = 0; tbUnionsIdx < tbUnions.Count; tbUnionsIdx++) { if (tbUnionsIdx > 0) { sb.Append(" \r\n\r\nUNION ALL\r\n\r\n"); } if (tbUnionsGt0) { sb.Append("select * from ("); } var tbUnion = tbUnions[tbUnionsIdx]; var sbnav = new StringBuilder(); sb.Append(_select); if (_distinct) { sb.Append("DISTINCT "); } if (_skip <= 0 && _limit > 0) { sb.Append("TOP ").Append(_limit).Append(" "); } sb.Append(field); sb.Append(" \r\nFROM "); var tbsjoin = _tables.Where(a => a.Type != SelectTableInfoType.From).ToArray(); var tbsfrom = _tables.Where(a => a.Type == SelectTableInfoType.From).ToArray(); for (var a = 0; a < tbsfrom.Length; a++) { sb.Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[a].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[a].Table.Type, tbsfrom[a].Alias) ?? tbsfrom[a].Alias); if (tbsjoin.Length > 0) { //如果存在 join 查询,则处理 from t1, t2 改为 from t1 inner join t2 on 1 = 1 for (var b = 1; b < tbsfrom.Length; b++) { sb.Append(" \r\nLEFT JOIN ").Append(_commonUtils.QuoteSqlName(tbUnion[tbsfrom[b].Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tbsfrom[b].Table.Type, tbsfrom[b].Alias) ?? tbsfrom[b].Alias); if (string.IsNullOrEmpty(tbsfrom[b].NavigateCondition) && string.IsNullOrEmpty(tbsfrom[b].On) && string.IsNullOrEmpty(tbsfrom[b].Cascade)) { sb.Append(" ON 1 = 1"); } else { sb.Append(" ON ").Append(tbsfrom[b].NavigateCondition ?? tbsfrom[b].On); if (string.IsNullOrEmpty(tbsfrom[b].Cascade) == false) { sb.Append(" AND (").Append(tbsfrom[b].Cascade).Append(")"); } } } break; } else { if (!string.IsNullOrEmpty(tbsfrom[a].NavigateCondition)) { sbnav.Append(" AND (").Append(tbsfrom[a].NavigateCondition).Append(")"); } if (!string.IsNullOrEmpty(tbsfrom[a].On)) { sbnav.Append(" AND (").Append(tbsfrom[a].On).Append(")"); } if (a > 0 && !string.IsNullOrEmpty(tbsfrom[a].Cascade)) { sbnav.Append(" AND (").Append(tbsfrom[a].Cascade).Append(")"); } } if (a < tbsfrom.Length - 1) { sb.Append(", "); } } foreach (var tb in tbsjoin) { if (tb.Type == SelectTableInfoType.Parent) { continue; } switch (tb.Type) { case SelectTableInfoType.LeftJoin: sb.Append(" \r\nLEFT JOIN "); break; case SelectTableInfoType.InnerJoin: sb.Append(" \r\nINNER JOIN "); break; case SelectTableInfoType.RightJoin: sb.Append(" \r\nRIGHT JOIN "); break; } sb.Append(_commonUtils.QuoteSqlName(tbUnion[tb.Table.Type])).Append(" ").Append(_aliasRule?.Invoke(tb.Table.Type, tb.Alias) ?? tb.Alias).Append(" ON ").Append(tb.On ?? tb.NavigateCondition); if (!string.IsNullOrEmpty(tb.Cascade)) { sb.Append(" AND (").Append(tb.Cascade).Append(")"); } if (!string.IsNullOrEmpty(tb.On) && !string.IsNullOrEmpty(tb.NavigateCondition)) { sbnav.Append(" AND (").Append(tb.NavigateCondition).Append(")"); } } if (_join.Length > 0) { sb.Append(_join); } sbnav.Append(_where); if (!string.IsNullOrEmpty(_tables[0].Cascade)) { sbnav.Append(" AND (").Append(_tables[0].Cascade).Append(")"); } if (sbnav.Length > 0) { sb.Append(" \r\nWHERE ").Append(sbnav.Remove(0, 5)); } if (string.IsNullOrEmpty(_groupby) == false) { sb.Append(_groupby); if (string.IsNullOrEmpty(_having) == false) { sb.Append(" \r\nHAVING ").Append(_having.Substring(5)); } } if (_skip > 0) { if (string.IsNullOrEmpty(_orderby)) { var pktb = _tables.Where(a => a.Table.Primarys.Any()).FirstOrDefault(); if (pktb != null) { _orderby = string.Concat(" \r\nORDER BY ", pktb.Alias, ".", _commonUtils.QuoteSqlName(pktb?.Table.Primarys.First().Attribute.Name)); } else { _orderby = string.Concat(" \r\nORDER BY ", _tables.First().Alias, ".", _commonUtils.QuoteSqlName(_tables.First().Table.Columns.First().Value.Attribute.Name)); } } sb.Append(_orderby).Append($" \r\nOFFSET {_skip} ROW"); if (_limit > 0) { sb.Append($" \r\nFETCH NEXT {_limit} ROW ONLY"); } } else { sb.Append(_orderby); } sbnav.Clear(); if (tbUnionsGt0) { sb.Append(") ftb"); } } return(sb.ToString()); }