protected override Expression VisitSelect(SqlSelectExpression selectExpression)
{
if (selectExpression.Skip != null)
{
var rowNumber = new SqlFunctionCallExpression(typeof(int), "ROW_NUMBER");
var over = new SqlOverExpression(rowNumber, selectExpression.OrderBy ?? new ReadOnlyList <Expression>(new [] { new SqlOrderByExpression(OrderType.Ascending, selectExpression.Columns[0].Expression) }));
var additionalColumn = new SqlColumnDeclaration(RowColumnName, over);
var newAlias = selectExpression.Alias + "INNER";
var innerColumns = new ReadOnlyList <SqlColumnDeclaration>(selectExpression.Columns.Select(c => c).Concat(new[] { additionalColumn }));
var innerSelect = new SqlSelectExpression(selectExpression.Type, newAlias, innerColumns, selectExpression.From, selectExpression.Where, null, selectExpression.GroupBy, selectExpression.Distinct, null, null, selectExpression.ForUpdate);
var outerColumns = selectExpression.Columns.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, newAlias, c.Name)));
Expression rowPredicate = Expression.GreaterThan(new SqlColumnExpression(typeof(int), newAlias, additionalColumn.Name), selectExpression.Skip);
if (selectExpression.Take != null && !(selectExpression.Take is SqlTakeAllValueExpression))
{
rowPredicate = Expression.And
(
rowPredicate,
Expression.LessThanOrEqual(new SqlColumnExpression(typeof(int), newAlias, additionalColumn.Name), Expression.Add(selectExpression.Skip, selectExpression.Take))
);
}
return(new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, outerColumns, innerSelect, rowPredicate, null, selectExpression.ForUpdate));
}
return(base.VisitSelect(selectExpression));
}
protected override Expression VisitSelect(SqlSelectExpression selectExpression)
{
var count = selectExpression.Columns.Count;
List <SqlColumnDeclaration> newColumns = null;
for (var i = 0; i < count; i++)
{
var column = selectExpression.Columns[i];
var visitedColumnExpression = this.Visit(column.Expression);
if (visitedColumnExpression.Type.GetUnwrappedNullableType() == typeof(bool) && !(visitedColumnExpression is BitBooleanExpression))
{
if (newColumns == null)
{
newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i));
}
var newColumnExpression = BitBooleanExpression.Coerce(visitedColumnExpression);
var newColumnDeclaration = new SqlColumnDeclaration(column.Name, newColumnExpression);
newColumns.Add(newColumnDeclaration);
}
else if (visitedColumnExpression != column.Expression)
{
if (newColumns == null)
{
newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i));
}
newColumns.Add(column.ReplaceExpression(visitedColumnExpression));
}
else if (newColumns != null)
{
newColumns.Add(column);
}
}
var from = this.VisitSource(selectExpression.From);
var where = this.Visit(selectExpression.Where);
var orderBy = this.VisitExpressionList(selectExpression.OrderBy);
var groupBy = this.VisitExpressionList(selectExpression.GroupBy);
var skip = this.Visit(selectExpression.Skip);
var take = this.Visit(selectExpression.Take);
if (where is BitBooleanExpression)
{
where = Expression.Equal(where, Expression.Constant(true, where.Type));
}
if (from != selectExpression.From || where != selectExpression.Where || newColumns != selectExpression.Columns || orderBy != selectExpression.OrderBy || groupBy != selectExpression.GroupBy || take != selectExpression.Take || skip != selectExpression.Skip)
{
return(new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, newColumns ?? selectExpression.Columns, from, where, orderBy, groupBy, selectExpression.Distinct, skip, take, selectExpression.ForUpdate, selectExpression.Reverse, selectExpression.Into));
}
return(base.VisitSelect(selectExpression));
}
protected virtual void VisitColumn(SqlSelectExpression selectExpression, SqlColumnDeclaration column)
{
var c = this.Visit(column.Expression) as SqlColumnExpression;
if ((c == null || c.Name != column.Name) && !String.IsNullOrEmpty(column.Name))
{
this.Write(" AS ");
this.WriteQuotedIdentifier(column.Name);
}
}
protected virtual SqlColumnDeclaration VisitColumnDeclaration(SqlColumnDeclaration sqlColumnDeclaration)
{
var e = this.Visit(sqlColumnDeclaration.Expression);
if (e != sqlColumnDeclaration.Expression)
{
return(new SqlColumnDeclaration(sqlColumnDeclaration.Name, e));
}
return(sqlColumnDeclaration);
}
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?.Cast <SqlOrderByExpression>().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, 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 newOrderBy = selectExpression
.OrderBy?
.Select(c => oldAliases == null ? c : AliasReferenceReplacer.Replace(c, oldAliases.Contains, innerSelectWithRowAlias))
.Concat(new SqlOrderByExpression(OrderType.Ascending, new SqlColumnExpression(typeof(int), innerSelectWithRowAlias, rowColumn.Name)))
.ToReadOnlyCollection();
var retval = new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, outerColumns, innerSelectWithRow, rowPredicate, newOrderBy, selectExpression.ForUpdate);
return(retval);
}
return(base.VisitSelect(selectExpression));
}
protected override void VisitColumn(SqlSelectExpression selectExpression, SqlColumnDeclaration column)
{
if (column.Expression.Type == typeof(Decimal))
{
this.Write("ROUND(CAST(");
var c = this.Visit(column.Expression) as SqlColumnExpression;
this.Write(" as NUMERIC)");
this.Write(", 20)");
if (!String.IsNullOrEmpty(column.Name))
{
this.Write(" AS ");
this.Write(this.identifierQuoteString);
this.Write(column.Name);
this.Write(this.identifierQuoteString);
}
}
else
{
base.VisitColumn(selectExpression, column);
}
}
protected override Expression VisitSelect(SqlSelectExpression select)
{
select = (SqlSelectExpression)base.VisitSelect(select);
if (this.aggregateSubqueriesBySelectAlias.Contains(select.Alias))
{
var columnsIncludingAggregates = new List <SqlColumnDeclaration>(select.Columns);
foreach (var aggregateSubqueryExpression in this.aggregateSubqueriesBySelectAlias[select.Alias])
{
var name = "AGGR" + columnsIncludingAggregates.Count;
var columnDeclaration = new SqlColumnDeclaration(name, aggregateSubqueryExpression.AggregateInGroupSelect);
this.aggregateSubqueryInstances.Add(aggregateSubqueryExpression, new SqlColumnExpression(aggregateSubqueryExpression.Type, aggregateSubqueryExpression.GroupByAlias, name));
columnsIncludingAggregates.Add(columnDeclaration);
}
return(new SqlSelectExpression(select.Type, select.Alias, columnsIncludingAggregates.ToReadOnlyList(), select.From, select.Where, select.OrderBy, select.GroupBy, select.Distinct, select.Skip, select.Take, select.ForUpdate));
}
return(select);
}
protected override Expression VisitSelect(SqlSelectExpression select)
{
// Visit column projection first
var columns = select.Columns;
List <SqlColumnDeclaration> alternate = null;
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
var columnDeclaration = select.Columns[i];
if (select.Distinct || this.IsColumnUsed(select.Alias, columnDeclaration.Name))
{
var expr = this.Visit(columnDeclaration.Expression);
if (expr != columnDeclaration.Expression)
{
columnDeclaration = new SqlColumnDeclaration(columnDeclaration.Name, expr);
}
}
else
{
columnDeclaration = null; // null means it gets omitted
}
if (columnDeclaration != select.Columns[i] && alternate == null)
{
alternate = new List <SqlColumnDeclaration>();
for (var j = 0; j < i; j++)
{
alternate.Add(select.Columns[j]);
}
}
if (columnDeclaration != null && alternate != null)
{
alternate.Add(columnDeclaration);
}
}
if (alternate != null)
{
columns = alternate.ToReadOnlyList();
}
var take = this.Visit(select.Take);
var skip = this.Visit(select.Skip);
var groupbys = this.VisitExpressionList(select.GroupBy);
var orderbys = this.VisitExpressionList(select.OrderBy);
var where = this.Visit(select.Where);
var from = this.Visit(select.From);
this.ClearColumnsUsed(select.Alias);
if (columns != select.Columns ||
orderbys != select.OrderBy ||
groupbys != select.GroupBy ||
where != select.Where ||
from != select.From ||
take != select.Take ||
skip != select.Skip)
{
select = new SqlSelectExpression(select.Type, select.Alias, columns, from, where, orderbys, groupbys, select.Distinct, skip, take, select.ForUpdate);
}
return(select);
}
protected override Expression VisitSelect(SqlSelectExpression select)
{
// Visit column projection first
var columns = select.Columns;
List <SqlColumnDeclaration> alternate = null;
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
var columnDeclaration = select.Columns[i];
if (select.Distinct || IsColumnUsed(select.Alias, columnDeclaration.Name) || columnDeclaration.NoOptimise)
{
var expr = Visit(columnDeclaration.Expression);
if (expr != columnDeclaration.Expression)
{
columnDeclaration = new SqlColumnDeclaration(columnDeclaration.Name, expr);
}
}
else
{
// null means it gets omitted
columnDeclaration = null;
}
if (columnDeclaration != select.Columns[i] && alternate == null)
{
alternate = new List <SqlColumnDeclaration>();
for (var j = 0; j < i; j++)
{
alternate.Add(select.Columns[j]);
}
}
if (columnDeclaration != null)
{
alternate?.Add(columnDeclaration);
}
}
if (alternate != null)
{
// Conservatively, don't remove any columns if it would mean removing all of them
if (alternate.Count == 0)
{
// Visit all columns so that their references/expressions can be marked as used
foreach (var c in select.Columns)
{
Visit(c.Expression);
}
}
else
{
columns = alternate.ToReadOnlyCollection();
}
}
var take = Visit(select.Take);
var skip = Visit(select.Skip);
var groupbys = VisitExpressionList(select.GroupBy);
var orderbys = VisitExpressionList(select.OrderBy);
var where = Visit(select.Where);
var from = Visit(select.From);
ClearColumnsUsed(select.Alias);
if (columns != select.Columns ||
orderbys != select.OrderBy ||
groupbys != select.GroupBy ||
where != select.Where ||
from != select.From ||
take != select.Take ||
skip != select.Skip)
{
select = new SqlSelectExpression(select.Type, select.Alias, columns, from, where, orderbys, groupbys, select.Distinct, skip, take, select.ForUpdate);
}
return(select);
}
protected override Expression VisitSelect(SqlSelectExpression selectExpression)
{
if (selectExpression.Skip != null)
{
const string orderByColumnPrefix = "__$$ORDERBYCOL";
var rowNumber = new SqlFunctionCallExpression(typeof(int), "ROW_NUMBER");
var selectWithRowAlias = selectExpression.Alias + "_ROW";
var cols = selectExpression
.Columns
.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, selectExpression.Alias, c.Name)))
.ToList();
var aliasedNames = selectExpression.OrderBy == null ? null : new HashSet <string>(selectExpression
.Columns
.Select(c => c.Expression)
.OfType <SqlColumnExpression>()
.Select(c => c.AliasedName));
var orderByCols = selectExpression
.OrderBy?
.Where(c => !IsColumnAndAlreadyProjected(c.Expression, aliasedNames))
.Select((c, i) => new SqlColumnDeclaration(orderByColumnPrefix + i, c.Expression)) ?? Enumerable.Empty <SqlColumnDeclaration>();
SqlOverExpression over;
if (selectExpression.OrderBy?.Any() == true)
{
var i = 0;
over = new SqlOverExpression(rowNumber, selectExpression
.OrderBy
.Select(c =>
{
if (IsColumnAndAlreadyProjected(c.Expression, aliasedNames))
{
return(new SqlOrderByExpression(c.OrderType, new SqlColumnExpression(c.Type, selectExpression.Alias, ((SqlColumnExpression)c.Expression).Name)));
}
else
{
return(new SqlOrderByExpression(c.OrderType, new SqlColumnExpression(c.Type, selectExpression.Alias, orderByColumnPrefix + i++)));
}
}).ToReadOnlyCollection());
}
else
{
over = new SqlOverExpression(rowNumber, cols.Select(c => new SqlOrderByExpression(OrderType.Ascending, c.Expression)).ToReadOnlyCollection());
}
var innerSelect = orderByCols == null ? selectExpression : selectExpression.ChangeColumns(selectExpression.Columns.Concat(orderByCols));
var rowColumn = new SqlColumnDeclaration(RowColumnName, over);
cols.Add(rowColumn);
var selectWithRowColumns = cols.ToReadOnlyCollection();
var selectWithRow = new SqlSelectExpression(selectExpression.Type, selectWithRowAlias, selectWithRowColumns, Visit(innerSelect.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, selectWithRowAlias, c.Name)));
Expression rowPredicate = Expression.GreaterThan(new SqlColumnExpression(typeof(int), selectWithRowAlias, rowColumn.Name), selectExpression.Skip);
if (selectExpression.Take != null && !(selectExpression.Take is SqlTakeAllValueExpression))
{
rowPredicate = Expression.And
(
rowPredicate,
Expression.LessThanOrEqual(new SqlColumnExpression(typeof(int), selectWithRowAlias, rowColumn.Name), Expression.Add(selectExpression.Skip, selectExpression.Take))
);
}
var retval = new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, outerColumns.ToReadOnlyCollection(), selectWithRow, 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)
{
List <SqlColumnDeclaration> newColumns = null;
for (var i = 0; i < selectExpression.Columns.Count; i++)
{
var column = selectExpression.Columns[i];
var visitedColumnExpression = this.Visit(column.Expression);
if (visitedColumnExpression.Type.GetUnwrappedNullableType() == typeof(bool) && !(visitedColumnExpression is BitBooleanExpression))
{
if (newColumns == null)
{
newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i));
}
var newColumnExpression = BitBooleanExpression.Coerce(visitedColumnExpression);
var newColumnDeclaration = new SqlColumnDeclaration(column.Name, newColumnExpression);
newColumns.Add(newColumnDeclaration);
}
else if (visitedColumnExpression != column.Expression)
{
if (newColumns == null)
{
newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i));
}
newColumns.Add(column.ReplaceExpression(visitedColumnExpression));
}
else if (newColumns != null)
{
newColumns.Add(column);
}
}
var where = this.Visit(selectExpression.Where);
if ((where is BitBooleanExpression))
{
where = Expression.Equal(where, Expression.Constant(true));
}
if (where != selectExpression.Where)
{
if (newColumns != null)
{
return(selectExpression.ChangeWhereAndColumns(where, new ReadOnlyList <SqlColumnDeclaration>(newColumns)));
}
else
{
return(selectExpression.ChangeWhere(where));
}
}
else if (newColumns != null)
{
return(selectExpression.ChangeColumns(newColumns, true));
}
else
{
return(base.VisitSelect(selectExpression));
}
}
