protected override Expression VisitSelect(SqlSelectExpression selectExpression) { if (selectExpression.OrderBy != null) { return selectExpression.ChangeOrderBy(null); } return base.VisitSelect(selectExpression); }
protected override Expression VisitSelect(SqlSelectExpression selectExpression) { if (selectExpression.Skip != null) { var rowNumber = new SqlFunctionCallExpression(typeof(int), "ROW_NUMBER"); var oldAliases = (selectExpression.From as ISqlExposesAliases)?.Aliases; var innerSelectWithRowAlias = selectExpression.Alias + "_ROW"; var cols = selectExpression.Columns.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, selectExpression.Alias, c.Name))).ToList(); var over = new SqlOverExpression(rowNumber, selectExpression.OrderBy?.ToReadOnlyCollection() ?? cols.Select(c => new SqlOrderByExpression(OrderType.Ascending, c.Expression)).ToReadOnlyCollection()); if (oldAliases != null) { over = (SqlOverExpression)AliasReferenceReplacer.Replace(over, oldAliases.Contains, selectExpression.Alias); } var rowColumn = new SqlColumnDeclaration(RowColumnName, over); cols.Add(rowColumn); var innerSelectWithRowColumns = cols.ToReadOnlyCollection(); var innerSelectWithRow = new SqlSelectExpression(selectExpression.Type, innerSelectWithRowAlias, innerSelectWithRowColumns, this.Visit(selectExpression.ChangeOrderBy(null).ChangeSkipTake(null, null)), null, null, null, false, null, null, false); var outerColumns = selectExpression.Columns.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, innerSelectWithRowAlias, c.Name))); Expression rowPredicate = Expression.GreaterThan(new SqlColumnExpression(typeof(int), innerSelectWithRowAlias, rowColumn.Name), selectExpression.Skip); if (selectExpression.Take != null && !(selectExpression.Take is SqlTakeAllValueExpression)) { rowPredicate = Expression.And ( rowPredicate, Expression.LessThanOrEqual(new SqlColumnExpression(typeof(int), innerSelectWithRowAlias, rowColumn.Name), Expression.Add(selectExpression.Skip, selectExpression.Take)) ); } var retval = new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, outerColumns.ToReadOnlyCollection(), innerSelectWithRow, rowPredicate, null, null, selectExpression.Distinct, null, null, selectExpression.ForUpdate, selectExpression.Reverse, selectExpression.Into); return retval; } return base.VisitSelect(selectExpression); }
protected override Expression VisitSelect(SqlSelectExpression selectExpression) { if (this.currentProjection == null || this.currentProjection.Select != selectExpression) { return base.VisitSelect(selectExpression); } var objectType = selectExpression.Type.GetSequenceElementType(); if (!objectType.IsDataAccessObjectType()) { return base.VisitSelect(selectExpression); } var aliasesAndTypes = SqlAliasTypeCollector.Collect(selectExpression) .ToDictionary(c => c.Item1, c => typeDescriptorProvider.GetTypeDescriptor(c.Item2.GetSequenceElementType() ?? c.Item2)); List<SqlOrderByExpression> orderBys = null; var includeJoins = selectExpression.From.GetIncludeJoins().ToList(); List<SqlColumnExpression> leftMostColumns = null; foreach (var includeJoin in includeJoins) { var equalsExpression = (BinaryExpression)SqlExpressionFinder.FindFirst(includeJoin, c => c.NodeType == ExpressionType.Equal); var left = (SqlColumnExpression)equalsExpression.Left; var right = (SqlColumnExpression)equalsExpression.Right; var leftType = aliasesAndTypes[left.SelectAlias]; var rightType = aliasesAndTypes[right.SelectAlias]; if (leftMostColumns == null) { var typeDescriptor = this.typeDescriptorProvider.GetTypeDescriptor(objectType); var primaryKeyColumns = new HashSet<string>(QueryBinder.GetPrimaryKeyColumnInfos(this.typeDescriptorProvider, typeDescriptor).Select(c => c.ColumnName)); leftMostColumns = primaryKeyColumns .Select(c => new SqlColumnExpression(objectType, left.SelectAlias, c)) .ToList(); } var rightProperty = rightType.GetPropertyDescriptorByColumnName(right.Name); var leftProperty = rightProperty.RelationshipInfo?.TargetProperty ?? leftType.GetPropertyDescriptorByColumnName(left.Name); if (leftProperty.PropertyType.GetGenericTypeDefinitionOrNull() == typeof(RelatedDataAccessObjects<>)) { var rightColumns = SqlExpressionFinder.FindAll(includeJoin, c => c.NodeType == (ExpressionType)SqlExpressionType.Column && ((SqlColumnExpression)c).SelectAlias == right.SelectAlias); var leftColumns = SqlExpressionFinder.FindAll(includeJoin, c => c.NodeType == (ExpressionType)SqlExpressionType.Column && ((SqlColumnExpression)c).SelectAlias == left.SelectAlias); if (orderBys == null) { orderBys = new List<SqlOrderByExpression>(); if (selectExpression.OrderBy?.Count > 0) { orderBys.AddRange(selectExpression.OrderBy); orderBys.AddRange(leftMostColumns.Select(c => new SqlOrderByExpression(OrderType.Ascending, c))); } } orderBys.AddRange(rightColumns.Select(c => new SqlOrderByExpression(OrderType.Ascending, c))); orderBys.AddRange(leftColumns.Select(c => new SqlOrderByExpression(OrderType.Ascending, c))); } } return selectExpression.ChangeOrderBy(orderBys?.Distinct(SqlExpressionEqualityComparer<SqlOrderByExpression>.Default) ?? selectExpression.OrderBy); }