public ShenTongUpdate(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public SqlServerDelete(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public ClickHouseCodeFirst(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
public MySqlCodeFirst(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
public OdbcKingbaseESDbFirst(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) { _orm = orm; _commonUtils = commonUtils; _commonExpression = commonExpression; }
public SqliteInsert(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
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 (_limit > 0) { sb.Append(" \r\nlimit ").Append(_limit); } if (_skip > 0) { sb.Append(" \r\noffset ").Append(_skip); } sbnav.Clear(); return(sb.ToString()); }
public MySqlDbFirst(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) { _orm = orm; _commonUtils = commonUtils; _commonExpression = commonExpression; }
public PostgreSQLInsert(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
public OdbcInsert(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { _utils = _commonUtils as OdbcUtils; }
public OdbcOracleSelect(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public MsAccessInsert(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { _batchAutoTransaction = false; }
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) => (_commonUtils as SqlServerUtils).IsSelectRowNumber ? ToSqlStaticRowNumber(_commonUtils, _commonExpression, _select, _distinct, field, _join, _where, _groupby, _having, _orderby, _skip, _limit, _tables, tableRuleInvoke, _whereCascadeExpression, _orm) : ToSqlStaticOffsetFetchNext(_commonUtils, _commonExpression, _select, _distinct, field, _join, _where, _groupby, _having, _orderby, _skip, _limit, _tables, tableRuleInvoke, _whereCascadeExpression, _orm);
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 "); } 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); } } 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); } } 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); } 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); } 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 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); 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(_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 (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.Append(_tosqlAppendContent).ToString()); }
public Select1Provider(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public DamengSelect(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public PostgreSQLDbFirst(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) { _orm = orm; _commonUtils = commonUtils; _commonExpression = commonExpression; }
public SqliteCodeFirst(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) { _orm = orm; _commonUtils = commonUtils; _commonExpression = commonExpression; }
public OdbcMySqlInsert(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
public PostgreSQLSelect(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public OracleInsertOrUpdate(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
public SqlServerInsertOrUpdate(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
public SelectGroupingProvider(IFreeSql orm, Select0Provider select, ReadAnonymousTypeInfo map, string field, CommonExpression comonExp, List <SelectTableInfo> tables) { _orm = orm; _select = select; _map = map; _field = field; _comonExp = comonExp; _tables = tables; }
public OdbcUpdate(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { _utils = _commonUtils as OdbcUtils; }
public OdbcKingbaseESDelete(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public KingbaseESInsertOrUpdate(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) : base(orm, commonUtils, commonExpression) { }
public OdbcGBaseUpdate(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression, object dywhere) : base(orm, commonUtils, commonExpression, dywhere) { }
public OdbcDamengDbFirst(IFreeSql orm, CommonUtils commonUtils, CommonExpression commonExpression) { _orm = orm; _commonUtils = commonUtils; _commonExpression = commonExpression; }
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, 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 (_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 { 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(")"); } } } } 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.Append(_tosqlAppendContent).ToString()); }