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