internal static string GetSelectAggregationStatement <T, TColumn>(string aggregateName, Expression <Func <T, TColumn> > projection, Expression <Func <T, bool> > predicate, DialectProvider dialect)
{
const string aliasRoot = "r";
var tableName = GetTableNameForSql(typeof(T), dialect) + " " + aliasRoot;
var whereClause = string.Empty;
if (predicate != null)
{
var evaluated = Evaluator.PartialEval(predicate);
evaluated = LocalCollectionExpander.Rewrite(evaluated);
var expression = PredicateVisitor.Visit <T>(evaluated, dialect, aliasRoot);
whereClause = string.Format(SqlWhereFormat, expression);
}
var memberExpression = (MemberExpression)projection.Body;
var parentPropertyMap = Reflector.GetPropertyMap(typeof(T));
parentPropertyMap.TryGetValue((PropertyInfo)memberExpression.Member, out var property);
var columnName = property != null ? property.MappedColumnName : memberExpression.Member.Name;
var sql = string.Format(SqlSelectAggregateFormat, aggregateName, dialect.IdentifierEscapeStartCharacter + columnName + dialect.IdentifierEscapeEndCharacter, tableName) + whereClause;
return(sql);
}
internal static string GetSelectCountStatement <T>(Expression <Func <T, bool> > predicate, DialectProvider dialect)
{
const string aliasRoot = "r";
var tableName = GetTableNameForSql(typeof(T), dialect) + " " + aliasRoot;
var whereClause = string.Empty;
if (predicate != null)
{
var evaluated = Evaluator.PartialEval(predicate);
evaluated = LocalCollectionExpander.Rewrite(evaluated);
var expression = PredicateVisitor.Visit <T>(evaluated, dialect, aliasRoot);
whereClause = string.Format(SqlWhereFormat, expression);
}
var sql = string.Format(SqlSelectCountFormat, tableName) + whereClause;
return(sql);
}
internal static string GetSelectStatement <T, T1, T2, T3, T4>(Expression <Func <T, bool> > predicate, Expression <Func <T, T1, bool> > join1,
Expression <Func <T1, T2, bool> > join2, Expression <Func <T2, T3, bool> > join3, Expression <Func <T3, T4, bool> > join4,
int page, int pageSize, int skipCount, bool first, DialectProvider dialect, params Sorting <T>[] orderBy)
where T : class
where T1 : class
where T2 : class
where T3 : class
where T4 : class
{
const string aliasRoot = "r";
var fake = typeof(ObjectFactory.Fake);
var types = new Dictionary <Type, LambdaExpression>();
if (typeof(T1) != fake && join1 != null)
{
types.Add(typeof(T1), join1);
}
if (typeof(T2) != fake && join2 != null)
{
types.Add(typeof(T2), join2);
}
if (typeof(T3) != fake && join3 != null)
{
types.Add(typeof(T3), join3);
}
if (typeof(T4) != fake && join4 != null)
{
types.Add(typeof(T4), join4);
}
var mapRoot = Reflector.GetPropertyNameMap <T>();
var selection = mapRoot.Values.Where(p => p.IsSelectable && p.IsSimpleType).Select(p => aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + p.MappedColumnName + dialect.IdentifierEscapeEndCharacter).ToDelimitedString(",");
var tableName = GetTableNameForSql(typeof(T), dialect) + " " + aliasRoot;
var index = 1;
var mapJoinLast = mapRoot;
var aliasJoinLast = aliasRoot;
foreach (var type in types)
{
var aliasJoin = "t" + index;
var tableNameJoin = GetTableNameForSql(type.Key, dialect) + " " + aliasJoin;
var mapJoin = Reflector.GetPropertyNameMap(type.Key);
var binaryExpression = (BinaryExpression)type.Value.Body;
var left = (MemberExpression)binaryExpression.Left;
var right = (MemberExpression)binaryExpression.Right;
var op = GetOperator(binaryExpression.NodeType);
tableName += string.Format(SqlInnerJoinClauseFormat, tableNameJoin,
aliasJoinLast + "." + dialect.IdentifierEscapeStartCharacter + mapJoinLast[left.Member.Name].MappedColumnName + dialect.IdentifierEscapeEndCharacter, op,
aliasJoin + "." + dialect.IdentifierEscapeStartCharacter + mapJoin[right.Member.Name].MappedColumnName + dialect.IdentifierEscapeEndCharacter);
mapJoinLast = mapJoin;
aliasJoinLast = aliasJoin;
index++;
}
if (types.Count > 0)
{
selection = mapJoinLast.Values.Where(p => p.IsSelectable && p.IsSimpleType).Select(p => aliasJoinLast + "." + dialect.IdentifierEscapeStartCharacter + p.MappedColumnName + dialect.IdentifierEscapeEndCharacter).ToDelimitedString(",");
}
var sql = string.Empty;
var whereClause = string.Empty;
if (predicate != null)
{
var evaluated = Evaluator.PartialEval(predicate);
evaluated = LocalCollectionExpander.Rewrite(evaluated);
var expression = PredicateVisitor.Visit <T>(evaluated, dialect, aliasRoot);
whereClause = string.Format(SqlWhereFormat, expression);
}
if (skipCount > 0 || (page > 0 && pageSize > 0))
{
if (dialect is SqlServerLegacyDialectProvider)
{
if (orderBy.Length == 0)
{
var primaryKeyAscending = mapRoot.Keys.Where(p => mapRoot[p].IsPrimaryKey)
.Select(p => aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[p].MappedColumnName + dialect.IdentifierEscapeEndCharacter + " ASC")
.ToDelimitedString(",");
var primaryKeyDescending = mapRoot.Keys.Where(p => mapRoot[p].IsPrimaryKey)
.Select(p => aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[p].MappedColumnName + dialect.IdentifierEscapeEndCharacter + " DESC")
.ToDelimitedString(",");
if (page > 0 && pageSize > 0)
{
sql = string.Format(SqlSelectPagingFormatMssqlLegacy, tableName, selection, primaryKeyAscending, primaryKeyDescending, whereClause, pageSize, page * pageSize);
}
else
{
throw new NotSupportedException();
}
}
else
{
var sort = new StringBuilder();
var sortReverse = new StringBuilder();
foreach (var o in orderBy)
{
var column = aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[((MemberExpression)o.OrderBy.Body).Member.Name].MappedColumnName + dialect.IdentifierEscapeEndCharacter;
sort.AppendFormat("{0} {1}, ", column, !o.Reverse ? "ASC" : "DESC");
sortReverse.AppendFormat("{0} {1}, ", column, !o.Reverse ? "DESC" : "ASC");
}
sort.Length -= 2;
sortReverse.Length -= 2;
if (page > 0 && pageSize > 0)
{
sql = string.Format(SqlSelectPagingFormatMssqlLegacy, tableName, selection, sort, sortReverse, whereClause, pageSize, page * pageSize);
}
else
{
throw new NotSupportedException();
}
}
}
else if (dialect is SqlServerDialectProvider || dialect is OracleDialectProvider)
{
var selectionWithoutAlias =
mapRoot.Values.Where(p => p.IsSelectable && p.IsSimpleType).Select(p => dialect.IdentifierEscapeStartCharacter + p.MappedColumnName + dialect.IdentifierEscapeEndCharacter).ToDelimitedString(",");
if (orderBy.Length == 0)
{
var primaryKey =
mapRoot.Keys.Where(p => mapRoot[p].IsPrimaryKey)
.Select(p => aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[p].MappedColumnName + dialect.IdentifierEscapeEndCharacter)
.ToDelimitedString(",");
if (page > 0 && pageSize > 0)
{
sql = string.Format(SqlSelectPagingFormatRowNumber, tableName, selection, primaryKey, whereClause, (page - 1) * pageSize, page * pageSize, selectionWithoutAlias);
}
else
{
sql = string.Format(SqlSelectSkipFormatRowNumber, tableName, selection, primaryKey, whereClause, skipCount, selectionWithoutAlias);
}
}
else
{
var sort = new StringBuilder();
foreach (var o in orderBy)
{
var column = aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[((MemberExpression)o.OrderBy.Body).Member.Name].MappedColumnName + dialect.IdentifierEscapeEndCharacter;
sort.AppendFormat("{0} {1}, ", column, !o.Reverse ? "ASC" : "DESC");
}
sort.Length -= 2;
if (page > 0 && pageSize > 0)
{
sql = string.Format(SqlSelectPagingFormatRowNumber, tableName, selection, sort, whereClause, (page - 1) * pageSize, page * pageSize, selectionWithoutAlias);
}
else
{
sql = string.Format(SqlSelectSkipFormatRowNumber, tableName, selection, sort, whereClause, skipCount, selectionWithoutAlias);
}
}
}
else if (dialect is SqlServerLatestDialectProvider)
{
if (orderBy.Length == 0)
{
var primaryKey =
mapRoot.Keys.Where(p => mapRoot[p].IsPrimaryKey)
.Select(p => aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[p].MappedColumnName + dialect.IdentifierEscapeEndCharacter)
.ToDelimitedString(",");
if (page > 0 && pageSize > 0)
{
sql = string.Format(SqlSelectPagingWithOrderByFormat, tableName, selection, whereClause, primaryKey, whereClause, (page - 1) * pageSize, pageSize);
}
else
{
sql = string.Format(SqlSelectSkipWithOrderByFormat, tableName, selection, whereClause, primaryKey, skipCount);
}
}
else
{
var sort = new StringBuilder();
foreach (var o in orderBy)
{
var column = aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[((MemberExpression)o.OrderBy.Body).Member.Name].MappedColumnName + dialect.IdentifierEscapeEndCharacter;
sort.AppendFormat("{0} {1}, ", column, !o.Reverse ? "ASC" : "DESC");
}
sort.Length -= 2;
if (page > 0 && pageSize > 0)
{
sql = string.Format(SqlSelectPagingWithOrderByFormat, tableName, selection, whereClause, sort, (page - 1) * pageSize, pageSize);
}
else
{
sql = string.Format(SqlSelectSkipWithOrderByFormat, tableName, selection, whereClause, sort, skipCount);
}
}
}
else
{
var orderByClause = "";
if (orderBy.Length > 0)
{
var sort = new StringBuilder(" ORDER BY ");
foreach (var o in orderBy)
{
var column = aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[((MemberExpression)o.OrderBy.Body).Member.Name].MappedColumnName + dialect.IdentifierEscapeEndCharacter;
sort.AppendFormat("{0} {1}, ", column, !o.Reverse ? "ASC" : "DESC");
}
sort.Length -= 2;
orderByClause = sort.ToString();
}
if (page > 0 && pageSize > 0)
{
sql = string.Format(SqlSelectPagingFormat, tableName, selection, whereClause, orderByClause, pageSize, (page - 1) * pageSize);
}
else
{
sql = string.Format(SqlSelectSkipFormat, tableName, selection, whereClause, orderByClause, skipCount);
}
}
}
else
{
sql = string.Format(SqlSelectFormat, tableName, selection) + whereClause;
if (orderBy.Length > 0)
{
var sort = new StringBuilder(" ORDER BY ");
foreach (var o in orderBy)
{
var column = aliasRoot + "." + dialect.IdentifierEscapeStartCharacter + mapRoot[((MemberExpression)o.OrderBy.Body).Member.Name].MappedColumnName + dialect.IdentifierEscapeEndCharacter;
sort.AppendFormat("{0} {1}, ", column, !o.Reverse ? "ASC" : "DESC");
}
sort.Length -= 2;
sql += sort;
}
}
if (first)
{
if (dialect is SqlServerDialectProvider)
{
sql = string.Format(SqlSelectFirstFormatMssql, sql);
}
else if (dialect is OracleDialectProvider)
{
sql = string.Format(SqlSelectFirstFormatOracle, sql);
}
else
{
sql = string.Format(SqlSelectFirstFormat, sql);
}
}
return(sql);
}