internal static StringBuilder GetQueryableSql <T>(Queryable <T> queryable)
{
string joinInfo = string.Join(" ", queryable.JoinTableValue);
StringBuilder sbSql = new StringBuilder();
string tableName = queryable.TableName.IsNullOrEmpty() ? queryable.TName : queryable.TableName;
if (queryable.DB.Language.IsValuable() && queryable.DB.Language.Suffix.IsValuable())
{
var viewNameList = LanguageHelper.GetLanguageViewNameList(queryable.DB);
var isLanView = viewNameList.IsValuable() && viewNameList.Any(it => it == tableName);
if (!queryable.DB.Language.Suffix.StartsWith(LanguageHelper.PreSuffix))
{
queryable.DB.Language.Suffix = LanguageHelper.PreSuffix + queryable.DB.Language.Suffix;
}
//将视图变更为多语言的视图
if (isLanView)
{
tableName = typeof(T).Name + queryable.DB.Language.Suffix;
}
}
if (queryable.DB.PageModel == PageModel.RowNumber)
{
#region rowNumber
string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null;
var order = queryable.OrderByValue.IsValuable() ? (",row_index=ROW_NUMBER() OVER(ORDER BY " + queryable.OrderByValue + " )") : null;
sbSql.AppendFormat("SELECT " + queryable.SelectValue.GetSelectFiles() + " {1} FROM {0} {5} {2} WHERE 1=1 {3} {4} ", tableName.GetTranslationSqlName(), order, withNoLock, string.Join("", queryable.WhereValue), queryable.GroupByValue.GetGroupBy(), joinInfo);
if (queryable.Skip == null && queryable.Take != null)
{
if (joinInfo.IsValuable())
{
sbSql.Insert(0, "SELECT * FROM ( ");
}
else
{
sbSql.Insert(0, "SELECT " + queryable.SelectValue.GetSelectFiles() + " FROM ( ");
}
sbSql.Append(") t WHERE t.row_index<=" + queryable.Take);
}
else if (queryable.Skip != null && queryable.Take == null)
{
if (joinInfo.IsValuable())
{
sbSql.Insert(0, "SELECT * FROM ( ");
}
else
{
sbSql.Insert(0, "SELECT " + queryable.SelectValue.GetSelectFiles() + " FROM ( ");
}
sbSql.Append(") t WHERE t.row_index>" + (queryable.Skip));
}
else if (queryable.Skip != null && queryable.Take != null)
{
if (joinInfo.IsValuable())
{
sbSql.Insert(0, "SELECT * FROM ( ");
}
else
{
sbSql.Insert(0, "SELECT " + queryable.SelectValue.GetSelectFiles() + " FROM ( ");
}
sbSql.Append(") t WHERE t.row_index BETWEEN " + (queryable.Skip + 1) + " AND " + (queryable.Skip + queryable.Take));
}
#endregion
}
else
{
#region offset
string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null;
var order = queryable.OrderByValue.IsValuable() ? ("ORDER BY " + queryable.OrderByValue + " ") : null;
sbSql.AppendFormat("SELECT " + queryable.SelectValue.GetSelectFiles() + " {1} FROM {0} {5} {2} WHERE 1=1 {3} {4} ", tableName.GetTranslationSqlName(), "", withNoLock, string.Join("", queryable.WhereValue), queryable.GroupByValue.GetGroupBy(), joinInfo);
sbSql.Append(order);
if (queryable.Skip != null || queryable.Take != null)
{
sbSql.AppendFormat("OFFSET {0} ROW FETCH NEXT {1} ROWS ONLY", Convert.ToInt32(queryable.Skip), Convert.ToInt32(queryable.Take));
}
#endregion
}
return(sbSql);
}