protected override void VisitQuery(ODataQueryExpression node) { const string RowNumberColumnName = "__medallionODataRowNumber"; var isCounting = node.InlineCount == ODataInlineCountOption.AllPages; // we never do pagination when doing counting. This is because, in OData, count ignores pagination var hasPagination = !isCounting && (node.Skip > 0 || node.Top.HasValue); // we special-case "top 0" and just do WHERE 1=0 instead. This is because offset-fetch // does not allow a fetch of 0 if (hasPagination && (node.Top ?? 1) == 0) { var emptyQuery = node.Update( filter: ODataExpression.Constant(false), top: null ); this.Visit(emptyQuery); return; } if (isCounting) { this.WriteLine("SELECT COUNT(1) AS theCount") .WriteLine("FROM ("); } var hasRowNumberPagination = hasPagination && this.syntaxProvider.Pagination == SqlSyntax.PaginationSyntax.RowNumber; if (hasRowNumberPagination) { this.Write("SELECT ") .WriteCommaDelimitedList(node.Select, ifEmpty: "*") .WriteLine() .WriteLine("FROM ("); } // select this.Write("SELECT "); if (hasRowNumberPagination) { this.Write("* , ROW_NUMBER() OVER (ORDER BY ") .WriteCommaDelimitedList(node.OrderBy, ifEmpty: "RAND()") .Write(") AS ").Write(RowNumberColumnName); } else { this.WriteCommaDelimitedList(node.Select, ifEmpty: "*"); } this.WriteLine(); // from this.Write("FROM ").Write(this.tableSql).Write(" ").Write(Alias).WriteLine(); // where if (node.Filter != null) { this.Write("WHERE ").Write(node.Filter, boolMode: BoolMode.Bool).WriteLine(); } if (hasRowNumberPagination) { this.Write(") ").WriteLine(Alias); // close the subquery this.Write("WHERE "); this.syntaxProvider.RenderParameterReference(s => this.Write(s), this.CreateParameter(ODataExpression.Constant(node.Skip))); this.Write(" < ").Write(Alias).Write(".").Write(RowNumberColumnName); if (node.Top.HasValue) { this.Write(" AND ").Write(Alias).Write(".").Write(RowNumberColumnName).Write(" <= "); this.syntaxProvider.RenderParameterReference(s => this.Write(s), this.CreateParameter(ODataExpression.Constant(node.Skip + node.Top.Value))); } this.WriteLine(); } // order by // we avoid rendering orderby when counting. We don't have to worry about pagination // since hasPagination is always false when counting if ((node.OrderBy.Count > 0 && !isCounting) // when doing offset-fetch pagination, we are required to have an order by clause || (hasPagination && this.syntaxProvider.Pagination == SqlSyntax.PaginationSyntax.OffsetFetch)) { this.Write("ORDER BY ") .WriteCommaDelimitedList(node.OrderBy, ifEmpty: "RAND()") .WriteLine(); } // skip/take if (hasPagination) { switch (this.syntaxProvider.Pagination) { case SqlSyntax.PaginationSyntax.OffsetFetch: this.Write("OFFSET "); this.syntaxProvider.RenderParameterReference(s => this.Write(s), this.CreateParameter(ODataExpression.Constant(node.Skip))); this.WriteLine(" ROWS"); if (node.Top.HasValue) { this.Write("FETCH NEXT "); this.syntaxProvider.RenderParameterReference(s => this.Write(s), this.CreateParameter(ODataExpression.Constant(node.Top.Value))); this.WriteLine(" ROWS ONLY"); } break; case SqlSyntax.PaginationSyntax.Limit: this.Write("LIMIT ").Write(node.Skip).Write(", ") .WriteLine(node.Top ?? "18446744073709551615".As <object>()); break; case SqlSyntax.PaginationSyntax.RowNumber: // handled above break; default: throw Throw.UnexpectedCase(this.syntaxProvider.Pagination); } } if (isCounting) { this.Write(") ").WriteLine(Alias); // close the subquery } }
private static ODataQueryExpression Take(ODataQueryExpression query, int take) { return(query.Update(top: Math.Min(query.Top ?? int.MaxValue, take))); }