public static string Translate(SqlSyntax syntaxProvider, string tableSql, ODataQueryExpression query, out List <Parameter> parameters)
{
Throw.IfNull(syntaxProvider, "databaseProvider");
Throw.If(string.IsNullOrEmpty(tableSql), "tableSql is required");
Throw.IfNull(query, "query");
var translator = new ODataToSqlTranslator(syntaxProvider, tableSql);
translator.Visit(query);
var sql = translator.sqlBuilder.ToString();
parameters = translator.parameters.Values.ToList();
return(sql);
}
public Result(ODataQueryExpression oDataQuery,
IQueryable <TElement> resultQuery = null,
IQueryable <TElement> inlineCountQuery = null,
IReadOnlyDictionary <ODataSelectColumnExpression, IReadOnlyList <PropertyInfo> > projectMapping = null,
IQueryable projectedResultQuery = null)
{
Throw.IfNull(oDataQuery, "oDataQuery");
Throw <ArgumentNullException> .If((resultQuery == null) != (inlineCountQuery == null), "resultQuery/inlineCountQuery: must be null together");
Throw <ArgumentNullException> .If((projectMapping == null) != (projectedResultQuery == null), "projectMapping/projectedResultQuery: must be null together");
Throw <ArgumentNullException> .If(projectMapping != null && inlineCountQuery == null, "projectMapping: if specified, inlineCountQuery is required");
this._oDataQuery = oDataQuery;
this._resultQuery = resultQuery;
this._inlineCountQuery = inlineCountQuery;
this._projectMapping = projectMapping;
this._projectedResultQuery = projectedResultQuery;
}
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)));
}
public TranslationResult(IQueryable rootQuery, ODataQueryExpression oDataQuery, Func <IODataDeserializationResult, object> postProcessor)
: base(rootQuery, oDataQuery, postProcessor)
{
}
public static IQueryable<TElement> Apply<TElement>(IQueryable<TElement> query, ODataQueryExpression oDataQuery, out IQueryable<TElement> inlineCountQuery)
{
var finalQuery = query;
if (oDataQuery.Filter != null)
{
var parameter = Expression.Parameter(typeof(TElement));
var predicate = Expression.Lambda<Func<TElement, bool>>(Translate(parameter, oDataQuery.Filter), parameter);
var denormalizedPredicate = (Expression<Func<TElement, bool>>)ODataEntity.Denormalize(predicate);
finalQuery = finalQuery.Where(denormalizedPredicate);
}
inlineCountQuery = finalQuery;
for (var i = 0; i < oDataQuery.OrderBy.Count; ++i)
{
finalQuery = ApplyOrderBy(finalQuery, oDataQuery.OrderBy[i], isPrimarySort: i == 0);
}
if (oDataQuery.Skip > 0)
{
finalQuery = finalQuery.Skip(oDataQuery.Skip);
}
if (oDataQuery.Top.HasValue)
{
finalQuery = finalQuery.Take(oDataQuery.Top.Value);
}
return finalQuery;
}
