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