/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="DbExpression"/></returns>
protected virtual DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
var projectionExpression = Visit(selectExpression.ProjectionExpression);
var fromExpression = Visit(selectExpression.FromExpression);
var whereExpression = Visit(selectExpression.WhereExpression);
var orderByExpression = Visit(selectExpression.OrderByExpression);
var groupByExpression = Visit(selectExpression.GroupByExpression);
var havingExpression = Visit(selectExpression.HavingExpression);
var takeExpression = Visit(selectExpression.TakeExpression);
var skipExpression = Visit(selectExpression.SkipExpression);
if (!ReferenceEquals(projectionExpression, selectExpression.ProjectionExpression) ||
!ReferenceEquals(fromExpression, selectExpression.FromExpression) ||
!ReferenceEquals(whereExpression, selectExpression.WhereExpression) ||
!ReferenceEquals(orderByExpression, selectExpression.OrderByExpression) ||
!ReferenceEquals(groupByExpression, selectExpression.GroupByExpression) ||
!ReferenceEquals(havingExpression, selectExpression.HavingExpression) ||
!ReferenceEquals(takeExpression, selectExpression.TakeExpression) ||
!ReferenceEquals(skipExpression, selectExpression.SkipExpression)
)
{
selectExpression.ProjectionExpression = projectionExpression;
selectExpression.FromExpression = fromExpression;
selectExpression.WhereExpression = whereExpression;
selectExpression.OrderByExpression = orderByExpression;
selectExpression.GroupByExpression = groupByExpression;
selectExpression.HavingExpression = havingExpression;
selectExpression.TakeExpression = takeExpression;
selectExpression.SkipExpression = skipExpression;
}
return(selectExpression);
}
private DbExpression BuildPagingStatement(DbSelectExpression selectExpression)
{
var sb = new StringBuilder();
DbExpression projectionExpression = Visit(selectExpression.ProjectionExpression);
sb.AppendFormat("SELECT {0} FROM (SELECT {0}, ", projectionExpression);
sb.Append("ROW_NUMBER() OVER (ORDER BY ");
if (!selectExpression.OrderByExpression.IsNull())
{
sb.Append(Visit(selectExpression.OrderByExpression));
}
else
{
sb.Append(Visit(selectExpression.ProjectionExpression));
}
sb.Append(") AS [ROW_NUMBER] ");
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
}
sb.Append(") AS __numbered__result ");
DbExpression skipSqlExpression = Visit(selectExpression.SkipExpression);
if (!selectExpression.TakeExpression.IsNull())
{
DbExpression takeSqlExpression = Visit(selectExpression.TakeExpression);
sb.AppendFormat("WHERE [ROW_NUMBER] BETWEEN {0} + 1 AND {0} + {1} ", skipSqlExpression,
takeSqlExpression);
}
else
{
sb.AppendFormat("WHERE [ROW_NUMBER] > {0} ", skipSqlExpression);
}
sb.Append("ORDER BY [ROW_NUMBER]");
if (selectExpression.IsSubQuery)
{
return(ExpressionFactory.Sql(string.Format("({0})", sb)));
}
return(ExpressionFactory.Sql(sb.ToString().Trim()));
}
/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="string"/></returns>
protected override DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
if (!selectExpression.SkipExpression.IsNull())
{
return(BuildPagingStatement(selectExpression));
}
var sb = new StringBuilder();
DbExpression projectionExpression = selectExpression.ProjectionExpression;
if (!projectionExpression.IsNull())
{
if (selectExpression.IsDistinct)
{
sb.Append("SELECT DISTINCT ");
}
else
{
sb.Append("SELECT ");
}
if (!selectExpression.TakeExpression.IsNull())
{
sb.AppendFormat("TOP({0}) ", Visit(selectExpression.TakeExpression));
}
sb.AppendLine();
sb.AppendFormat(1, "{0} ", Visit(projectionExpression));
}
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
}
if (!selectExpression.GroupByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("GROUP BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.GroupByExpression));
}
if (!selectExpression.HavingExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("HAVING ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.HavingExpression));
}
if (!selectExpression.OrderByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("ORDER BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.OrderByExpression));
}
if (selectExpression.IsSubQuery)
{
return(ExpressionFactory.Sql(string.Format("({0})", sb.ToString().Trim())));
}
return(ExpressionFactory.Sql(sb.ToString().Trim()));
}
/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="string"/></returns>
protected override DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
var sb = new StringBuilder();
string pagingSyntax = null;
if (ContainsSkipAndTake(selectExpression))
pagingSyntax = string.Format("ROWNUM <= {0} AND ROWNUM > {1} ", Visit(selectExpression.TakeExpression), Visit(selectExpression.SkipExpression));
else if (ContainsTake(selectExpression))
pagingSyntax = string.Format("ROWNUM <= {0} ", Visit(selectExpression.TakeExpression));
else if (ContainsSkip(selectExpression))
pagingSyntax = string.Format("ROWNUM > {0} ", Visit(selectExpression.SkipExpression));
if (!selectExpression.ProjectionExpression.IsNull())
{
if (selectExpression.IsDistinct)
sb.Append("SELECT DISTINCT ");
else
sb.Append("SELECT ");
sb.AppendLine();
sb.AppendFormat(1, "{0} ", Visit(selectExpression.ProjectionExpression));
}
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
if (pagingSyntax != null)
sb.AppendFormat(1, "AND {0} ", pagingSyntax);
}
else
{
if (pagingSyntax != null)
{
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", pagingSyntax);
}
}
if (!selectExpression.GroupByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("GROUP BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.GroupByExpression));
}
if (!selectExpression.HavingExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("HAVING ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.HavingExpression));
}
if (!selectExpression.OrderByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("ORDER BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.OrderByExpression));
}
if (selectExpression.IsSubQuery)
return ExpressionFactory.Sql(string.Format("({0})", sb.ToString().Trim()));
return ExpressionFactory.Sql(sb.ToString().Trim());
}
private static bool ContainsTake(DbSelectExpression selectExpression)
{
return !selectExpression.TakeExpression.IsNull() && selectExpression.SkipExpression.IsNull();
}
private static bool ContainsSkipAndTake(DbSelectExpression selectExpression)
{
return(!selectExpression.TakeExpression.IsNull() && !selectExpression.SkipExpression.IsNull());
}
/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="DbExpression"/></returns>
protected virtual DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
var projectionExpression = Visit(selectExpression.ProjectionExpression);
var fromExpression = Visit(selectExpression.FromExpression);
var whereExpression = Visit(selectExpression.WhereExpression);
var orderByExpression = Visit(selectExpression.OrderByExpression);
var groupByExpression = Visit(selectExpression.GroupByExpression);
var havingExpression = Visit(selectExpression.HavingExpression);
var takeExpression = Visit(selectExpression.TakeExpression);
var skipExpression = Visit(selectExpression.SkipExpression);
if (!ReferenceEquals(projectionExpression,selectExpression.ProjectionExpression) ||
!ReferenceEquals(fromExpression, selectExpression.FromExpression) ||
!ReferenceEquals(whereExpression, selectExpression.WhereExpression) ||
!ReferenceEquals(orderByExpression, selectExpression.OrderByExpression) ||
!ReferenceEquals(groupByExpression, selectExpression.GroupByExpression) ||
!ReferenceEquals(havingExpression, selectExpression.HavingExpression) ||
!ReferenceEquals(takeExpression, selectExpression.TakeExpression) ||
!ReferenceEquals(skipExpression, selectExpression.SkipExpression)
)
{
selectExpression.ProjectionExpression = projectionExpression;
selectExpression.FromExpression = fromExpression;
selectExpression.WhereExpression = whereExpression;
selectExpression.OrderByExpression = orderByExpression;
selectExpression.GroupByExpression = groupByExpression;
selectExpression.HavingExpression = havingExpression;
selectExpression.TakeExpression = takeExpression;
selectExpression.SkipExpression = skipExpression;
}
return selectExpression;
}
/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="string"/></returns>
protected override DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
var sb = new StringBuilder();
string pagingSyntax = null;
if (ContainsSkipAndTake(selectExpression))
{
pagingSyntax = string.Format("ROWNUM <= {0} AND ROWNUM > {1} ", Visit(selectExpression.TakeExpression), Visit(selectExpression.SkipExpression));
}
else if (ContainsTake(selectExpression))
{
pagingSyntax = string.Format("ROWNUM <= {0} ", Visit(selectExpression.TakeExpression));
}
else if (ContainsSkip(selectExpression))
{
pagingSyntax = string.Format("ROWNUM > {0} ", Visit(selectExpression.SkipExpression));
}
if (!selectExpression.ProjectionExpression.IsNull())
{
if (selectExpression.IsDistinct)
{
sb.Append("SELECT DISTINCT ");
}
else
{
sb.Append("SELECT ");
}
sb.AppendLine();
sb.AppendFormat(1, "{0} ", Visit(selectExpression.ProjectionExpression));
}
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
if (pagingSyntax != null)
{
sb.AppendFormat(1, "AND {0} ", pagingSyntax);
}
}
else
{
if (pagingSyntax != null)
{
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", pagingSyntax);
}
}
if (!selectExpression.GroupByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("GROUP BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.GroupByExpression));
}
if (!selectExpression.HavingExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("HAVING ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.HavingExpression));
}
if (!selectExpression.OrderByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("ORDER BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.OrderByExpression));
}
if (selectExpression.IsSubQuery)
{
return(ExpressionFactory.Sql(string.Format("({0})", sb.ToString().Trim())));
}
return(ExpressionFactory.Sql(sb.ToString().Trim()));
}
private DbExpression BuildPagingStatement(DbSelectExpression selectExpression)
{
var sb = new StringBuilder();
DbExpression projectionExpression = Visit(selectExpression.ProjectionExpression);
sb.AppendFormat("SELECT {0} FROM (SELECT {0}, ", projectionExpression);
sb.Append("ROW_NUMBER() OVER (ORDER BY ");
if (!selectExpression.OrderByExpression.IsNull() )
sb.Append(Visit(selectExpression.OrderByExpression));
else
sb.Append(Visit(selectExpression.ProjectionExpression));
sb.Append(") AS [ROW_NUMBER] ");
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
}
sb.Append(") AS __numbered__result ");
DbExpression skipSqlExpression = Visit(selectExpression.SkipExpression);
if (!selectExpression.TakeExpression.IsNull())
{
DbExpression takeSqlExpression = Visit(selectExpression.TakeExpression);
sb.AppendFormat("WHERE [ROW_NUMBER] BETWEEN {0} + 1 AND {0} + {1} ", skipSqlExpression,
takeSqlExpression);
}
else
sb.AppendFormat("WHERE [ROW_NUMBER] > {0} ", skipSqlExpression);
sb.Append("ORDER BY [ROW_NUMBER]");
if (selectExpression.IsSubQuery)
return ExpressionFactory.Sql(string.Format("({0})", sb));
return ExpressionFactory.Sql(sb.ToString().Trim());
}
/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="string"/></returns>
protected override DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
if (!selectExpression.SkipExpression.IsNull())
return BuildPagingStatement(selectExpression);
var sb = new StringBuilder();
DbExpression projectionExpression = selectExpression.ProjectionExpression;
if (!projectionExpression.IsNull())
{
if (selectExpression.IsDistinct)
sb.Append("SELECT DISTINCT ");
else
sb.Append("SELECT ");
if (!selectExpression.TakeExpression.IsNull())
sb.AppendFormat("TOP({0}) ", Visit(selectExpression.TakeExpression));
sb.AppendLine();
sb.AppendFormat(1, "{0} ", Visit(projectionExpression));
}
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
}
if (!selectExpression.GroupByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("GROUP BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.GroupByExpression));
}
if (!selectExpression.HavingExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("HAVING ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.HavingExpression));
}
if (!selectExpression.OrderByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("ORDER BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.OrderByExpression));
}
if (selectExpression.IsSubQuery)
return ExpressionFactory.Sql(string.Format("({0})", sb.ToString().Trim()));
return ExpressionFactory.Sql(sb.ToString().Trim());
}
/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="string"/></returns>
protected override DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
var sb = new StringBuilder();
if (!selectExpression.ProjectionExpression.IsNull())
{
if (selectExpression.IsDistinct)
sb.Append("SELECT DISTINCT ");
else
sb.Append("SELECT ");
sb.AppendLine();
sb.AppendFormat(1, "{0} ", Visit(selectExpression.ProjectionExpression));
}
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
}
if (!selectExpression.GroupByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("GROUP BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.GroupByExpression));
}
if (!selectExpression.HavingExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("HAVING ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.HavingExpression));
}
if (!selectExpression.OrderByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("ORDER BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.OrderByExpression));
}
if (ContainsSkipAndTake(selectExpression))
sb.AppendFormat("LIMIT {0} OFFSET {1} ", Visit(selectExpression.TakeExpression), Visit(selectExpression.SkipExpression));
else if (ContainsTake(selectExpression))
sb.AppendFormat("LIMIT {0} ", Visit(selectExpression.TakeExpression));
else if (ContainsSkip(selectExpression))
sb.AppendFormat("LIMIT {0} OFFSET 18446744073709551615 ", Visit(selectExpression.SkipExpression));
if (selectExpression.IsSubQuery)
return ExpressionFactory.Sql(string.Format("({0})", sb.ToString().Trim()));
return ExpressionFactory.Sql(sb.ToString().Trim());
}
/// <summary>
/// Translates the <paramref name="selectExpression"/> into a string representation.
/// </summary>
/// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
/// <returns><see cref="string"/></returns>
protected override DbExpression VisitSelectExpression(DbSelectExpression selectExpression)
{
var sb = new StringBuilder();
if (!selectExpression.ProjectionExpression.IsNull())
{
if (selectExpression.IsDistinct)
{
sb.Append("SELECT DISTINCT ");
}
else
{
sb.Append("SELECT ");
}
sb.AppendLine();
sb.AppendFormat(1, "{0} ", Visit(selectExpression.ProjectionExpression));
}
if (!selectExpression.FromExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("FROM ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
}
if (!selectExpression.WhereExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("WHERE ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
}
if (!selectExpression.GroupByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("GROUP BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.GroupByExpression));
}
if (!selectExpression.HavingExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("HAVING ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.HavingExpression));
}
if (!selectExpression.OrderByExpression.IsNull())
{
sb.AppendLine();
sb.AppendLine("ORDER BY ");
sb.AppendFormat(1, "{0} ", Visit(selectExpression.OrderByExpression));
}
if (ContainsSkipAndTake(selectExpression))
{
sb.AppendFormat("LIMIT {0} OFFSET {1} ", Visit(selectExpression.TakeExpression), Visit(selectExpression.SkipExpression));
}
else if (ContainsTake(selectExpression))
{
sb.AppendFormat("LIMIT {0} ", Visit(selectExpression.TakeExpression));
}
else if (ContainsSkip(selectExpression))
{
sb.AppendFormat("LIMIT {0} OFFSET 18446744073709551615 ", Visit(selectExpression.SkipExpression));
}
if (selectExpression.IsSubQuery)
{
return(ExpressionFactory.Sql(string.Format("({0})", sb.ToString().Trim())));
}
return(ExpressionFactory.Sql(sb.ToString().Trim()));
}