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