/// <summary>
/// Visits the column declarations.
/// </summary>
/// <param name="columns">The columns.</param>
/// <returns></returns>
protected virtual ReadOnlyCollection <ColumnDeclaration> VisitColumnDeclarations(ReadOnlyCollection <ColumnDeclaration> columns)
{
List <ColumnDeclaration> alternate = null;
for (int i = 0, n = columns.Count; i < n; i++)
{
ColumnDeclaration column = columns[i];
Expression e = this.Visit(column.Expression);
if (alternate == null && e != column.Expression)
{
alternate = columns.Take(i).ToList();
}
if (alternate != null)
{
alternate.Add(new ColumnDeclaration(column.Name, e));
}
}
if (alternate != null)
{
return(alternate.AsReadOnly());
}
return(columns);
}
public static SelectExpression RemoveColumn(this SelectExpression select, ColumnDeclaration column)
{
List <ColumnDeclaration> columns = new List <ColumnDeclaration>(select.Columns);
columns.Remove(column);
return(select.SetColumns(columns));
}
protected override Expression VisitSelect(SelectExpression select)
{
select = (SelectExpression)base.VisitSelect(select);
if (lookup.Contains(select.Alias))
{
List<ColumnDeclaration> aggColumns = new List<ColumnDeclaration>(select.Columns);
foreach (AggregateSubqueryExpression ae in lookup[select.Alias])
{
string name = "agg" + aggColumns.Count;
var colType = DbTypeSystem.GetColumnType(ae.Type);
ColumnDeclaration cd = new ColumnDeclaration(name, ae.AggregateInGroupSelect, colType);
this.map.Add(ae, new ColumnExpression(ae.Type, colType, ae.GroupByAlias, name));
aggColumns.Add(cd);
}
return new SelectExpression(
select.Alias,
aggColumns,
select.From,
select.Where,
select.OrderBy,
select.GroupBy,
select.IsDistinct,
select.Skip,
select.Take,
select.IsReverse);
}
return select;
}
/// <summary>
/// Writes the columns.
/// </summary>
/// <param name="columns">The columns.</param>
protected virtual void WriteColumns(ReadOnlyCollection <ColumnDeclaration> columns)
{
if (columns.Count > 0)
{
for (int i = 0, n = columns.Count; i < n; i++)
{
ColumnDeclaration column = columns[i];
if (i > 0)
{
this.Write(", ");
}
ColumnExpression c = this.VisitValue(column.Expression) as ColumnExpression;
if (!string.IsNullOrEmpty(column.Name) && (c == null || c.Name != column.Name))
{
this.Write(" ");
this.WriteAsColumnName(column.Name);
}
}
}
else
{
this.Write("NULL ");
if (this.IsNested)
{
this.WriteAsColumnName("tmp");
this.Write(" ");
}
}
}
protected override Expression VisitSelect(SelectExpression select)
{
// visit column projection first
ReadOnlyCollection <ColumnDeclaration> columns = select.Columns;
if (select.Alias != null && _allColumnsUsed.TryGetValue(select.Alias, out HashSet <string> columnsUsed))
{
List <ColumnDeclaration> alternate = null;
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration decl = select.Columns[i];
if (select.IsDistinct || columnsUsed.Contains(decl.Name))
{
if (Visit(decl.Expression) != decl.Expression)
{
decl = new ColumnDeclaration(decl.Name, decl.Expression);
}
}
else
{
decl = null; // null means it gets omitted
}
if (decl != select.Columns[i] && alternate == null)
{
alternate = select.Columns.Take(i).ToList();
}
if (decl != null)
{
alternate?.Add(decl);
}
}
if (alternate != null)
{
columns = alternate.AsReadOnly();
}
}
Expression offset = Visit(select.Offset);
Expression limit = Visit(select.Limit);
Expression having = Visit(select.Having);
ReadOnlyCollection <Expression> groupBys = select.GroupBy == null ? null : Visit(select.GroupBy);
ReadOnlyCollection <OrderExpression> orderbys = VisitOrderBy(select.OrderBy);
Expression where = Visit(select.Where);
Expression from = Visit(select.From);
ClearColumnsUsed(select.Alias);
if (columns != select.Columns || orderbys != select.OrderBy || groupBys != select.GroupBy ||
having != select.Having || limit != select.Limit || offset != select.Offset ||
where != select.Where || from != select.From)
{
return(new SelectExpression(select.Alias, columns, from, where,
groupBys, having, orderbys,
offset, limit, select.IsDistinct));
}
return(select);
}
protected override Expression VisitSelect(SelectExpression select)
{
// visit column projection first
ReadOnlyCollection <ColumnDeclaration> columns = select.Columns;
HashSet <string> columnsUsed;
if (_allColumnsUsed.TryGetValue(select.Alias, out columnsUsed))
{
List <ColumnDeclaration> alternate = null;
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration decl = select.Columns[i];
if (!columnsUsed.Contains(decl.Name))
{
decl = null; // null means it gets omitted
}
else
{
Expression expr = Visit(decl.Expression);
if (expr != decl.Expression)
{
decl = new ColumnDeclaration(decl.Name, decl.Expression);
}
}
if (decl != select.Columns[i] && alternate == null)
{
alternate = new List <ColumnDeclaration>();
for (int j = 0; j < i; j++)
{
alternate.Add(select.Columns[j]);
}
}
if (decl != null && alternate != null)
{
alternate.Add(decl);
}
}
if (alternate != null)
{
columns = alternate.AsReadOnly();
}
}
//ReadOnlyCollection<OrderExpression> orderbys = this.VisitOrderBy(select.OrderBy);
Expression where = Visit(select.Where);
Expression from = Visit(select.From);
if (columns != select.Columns || /*orderbys != select.OrderBy ||*/ where != select.Where || from != select.From)
{
return(new SelectExpression(select.Type, select.Alias, columns, from, where)); //, orderbys);
}
return(select);
}
protected internal virtual ColumnDeclaration VisitColumnDeclaration(ColumnDeclaration c)
{
var e = Visit(c.Expression);
if (e == c.Expression)
{
return(c);
}
return(new ColumnDeclaration(c.Name, e));
}
protected override Expression VisitSelect(SelectExpression select)
{
sb.Append("Select ");
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration column = select.Columns[i];
if (i > 0)
{
sb.Append(" ,");
}
ColumnExpression c = this.Visit(column.Expression) as ColumnExpression;
if (c == null || c.Name != select.Columns[i].Name)
{
sb.Append(" As ");
sb.Append(column.Name);
}
}
if (select.From != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append(" FROM ");
this.VisitSource(select.From);
}
if (select.Where != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append(" WHERE ");
this.Visit(select.Where);
}
if (select.OrderBy != null && select.OrderBy.Count > 0)
{
this.AppendNewLine(Indentation.Same);
sb.Append(" ORDER BY ");
//当有多个排序字段时
for (int i = 0, n = select.OrderBy.Count; i < n; i++)
{
OrderExpression expr = select.OrderBy[i];
if (i > 0)
{
sb.Append(",");
}
this.Visit(expr.Expression);
if (expr.OrderType != OrderType.Ascending)
{
sb.Append(" DESC ");
}
}
}
return(select);
}
public override ProjectionExpression GetQueryExpression(MappingEntity entity)
{
CompositiveMappingEntity mme = (CompositiveMappingEntity)entity;
if (mme.Table == null || string.IsNullOrEmpty(mme.Table.View))
{
var tableAlias = new TableAlias();
var selectAlias = new TableAlias();
var columns = new List <ColumnDeclaration>();
var aliases = new Dictionary <string, TableAlias>();
var table = new TableExpression(tableAlias, entity, (this.Mapping as BasicMapping).GetTableName(entity));
this.GetColumns(entity, aliases, columns);
SelectExpression root = new SelectExpression(new TableAlias(), columns, table, null);
Expression projector = this.GetEntityExpression(table, entity);
var pc = ColumnProjector.ProjectColumns(this.Translator.Linguist.Language, projector, null, selectAlias, tableAlias);
var proj = new ProjectionExpression(
new SelectExpression(selectAlias, pc.Columns, table, null),
pc.Projector
);
return((ProjectionExpression)this.Translator.Police.ApplyPolicy(proj, entity.ElementType));
}
else
{
var tableAlias = new TableAlias();
var selectAlias = new TableAlias();
var columns = new List <ColumnDeclaration>();
var aliases = new Dictionary <string, TableAlias>();
var table = new TableExpression(tableAlias, entity, mme.Table.View);
ColumnDeclaration cd = new ColumnDeclaration("*", "", new AllColumnExpression(tableAlias), new DbQueryType(SqlDbType.Variant, false, 0, 0, 0));
columns.Add(cd);
SelectExpression root = new SelectExpression(new TableAlias(), columns, table, null);
Expression projector = this.GetEntityExpression(table, entity);
//var pc = ColumnProjector.ProjectColumns(this.Translator.Linguist.Language, projector, null, selectAlias, tableAlias);
var proj = new ProjectionExpression(
new SelectExpression(selectAlias, columns, table, null),
projector
);
return((ProjectionExpression)this.Translator.Police.ApplyPolicy(proj, entity.ElementType));
}
}
protected internal override Expression VisitSelect(SelectExpression select)
{
select = (SelectExpression)base.VisitSelect(select);
if (lookup.Contains(select.Alias))
{
List <ColumnDeclaration> aggColumns = new List <ColumnDeclaration>(select.Columns);
foreach (AggregateRequestsExpression ae in lookup[select.Alias])
{
ColumnDeclaration cd = new ColumnDeclaration("agg" + aggColumns.Count, ae.Aggregate);
this.map.Add(ae, cd.GetReference(ae.GroupByAlias));
aggColumns.Add(cd);
}
return(new SelectExpression(select.Alias, select.IsDistinct, select.Top, aggColumns, select.From, select.Where, select.OrderBy, select.GroupBy, select.SelectOptions));
}
return(select);
}
protected internal override Expression VisitSelect(SelectExpression select)
{
select = (SelectExpression)base.VisitSelect(select);
if (lookup.Contains(select.Alias))
{
List<ColumnDeclaration> aggColumns = new List<ColumnDeclaration>(select.Columns);
foreach (AggregateRequestsExpression ae in lookup[select.Alias])
{
ColumnDeclaration cd = new ColumnDeclaration("agg" + aggColumns.Count, ae.Aggregate);
this.map.Add(ae, cd.GetReference(ae.GroupByAlias));
aggColumns.Add(cd);
}
return new SelectExpression(select.Alias, select.IsDistinct, select.Top, aggColumns, select.From, select.Where, select.OrderBy, select.GroupBy, select.SelectOptions);
}
return select;
}
protected override Expression VisitSelect(SelectExpression select)
{
select = (SelectExpression)base.VisitSelect(select);
// look for redundant column declarations
List <ColumnDeclaration> cols = select.Columns.OrderBy(c => c.Name).ToList();
BitArray removed = new BitArray(select.Columns.Count);
bool anyRemoved = false;
for (int i = 0, n = cols.Count; i < n - 1; i++)
{
ColumnDeclaration ci = cols[i];
ColumnExpression cix = ci.Expression as ColumnExpression;
ColumnExpression cxi = new ColumnExpression(ci.Expression.Type, select.Alias, ci.Name, cix != null ? cix.MapInfo : null);
for (int j = i + 1; j < n; j++)
{
if (!removed.Get(j))
{
ColumnDeclaration cj = cols[j];
if (SameExpression(ci.Expression, cj.Expression))
{
// any reference to 'j' should now just be a reference to 'i'
ColumnExpression cj1 = cj.Expression as ColumnExpression;
ColumnExpression cxj = new ColumnExpression(cj.Expression.Type, select.Alias, cj.Name, cj1 != null ? cj1.MapInfo : null);
this.map.Add(cxj, cxi);
removed.Set(j, true);
anyRemoved = true;
}
}
}
}
if (anyRemoved)
{
List <ColumnDeclaration> newDecls = new List <ColumnDeclaration>();
for (int i = 0, n = cols.Count; i < n; i++)
{
if (!removed.Get(i))
{
newDecls.Add(cols[i]);
}
}
select = select.SetColumns(newDecls);
}
return(select);
}
internal void GetColumns(IEntityMapping mapping, Dictionary <string, TableAlias> aliases, List <ColumnDeclaration> columns)
{
foreach (var mi in mapping.Members)
{
if (!mi.IsRelationship && mi.IsColumn)
{
string name = mi.ColumnName;
string aliasName = mi.AliasName;
TableAlias alias;
aliases.TryGetValue(aliasName, out alias);
var colType = mi.SqlType;
ColumnExpression ce = new ColumnExpression(mi.MemberType, colType, alias, name);
ColumnDeclaration cd = new ColumnDeclaration(name, ce, colType);
columns.Add(cd);
}
}
}
protected override Expression VisitSelect(SelectExpression select)
{
select = (SelectExpression)base.VisitSelect(select);
if (lookup.Contains(select.Alias))
{
List <ColumnDeclaration> aggColumns = new List <ColumnDeclaration>(select.Columns);
foreach (AggregateSubqueryExpression ae in lookup[select.Alias])
{
string name = "agg" + aggColumns.Count;
ColumnDeclaration cd = new ColumnDeclaration(name, ae.AggregateInGroupSelect);
this.map.Add(ae, new ColumnExpression(ae.Type, ae.GroupByAlias, name));
aggColumns.Add(cd);
}
return(new SelectExpression(select.Alias, aggColumns, select.From, select.Where, select.OrderBy, select.GroupBy, select.IsDistinct, select.Skip, select.Take));
}
return(select);
}
protected override void WriteColumns(System.Collections.ObjectModel.ReadOnlyCollection <ColumnDeclaration> columns)
{
if (columns.Count == 0)
{
this.Write("0");
}
else
{
for (int i = 0, n = columns.Count; i < n; i++)
{
ColumnDeclaration column = columns[i];
if (i > 0)
{
this.Write(", ");
}
var dbQueryType = column.QueryType as DbQueryType;
var vfpDbQueryType = dbQueryType as VfpDbQueryType;
var isDecimal = dbQueryType != null && dbQueryType.DbType == System.Data.DbType.Decimal;
if (vfpDbQueryType != null && isDecimal)
{
this.Write(" CAST(");
this.VisitValue(column.Expression);
this.Write(" AS N(20, ");
this.Write(dbQueryType.Scale);
this.Write(")) ");
if (!string.IsNullOrEmpty(column.Name))
{
this.WriteAsColumnName(column.Name);
}
}
else
{
ColumnExpression c = this.VisitValue(column.Expression) as ColumnExpression;
if (!string.IsNullOrEmpty(column.Name) && (c == null || c.Name != column.Name))
{
this.Write(" ");
this.WriteAsColumnName(column.Name);
}
}
}
}
}
protected override Expression VisitSelect(SelectExpression select)
{
select = (SelectExpression)base.VisitSelect(select);
if (lookup.Contains(select.Alias))
{
var aggColumns = new List <ColumnDeclaration>(select.Columns);
foreach (var ae in lookup[select.Alias])
{
var name = "agg" + aggColumns.Count;
var cd = new ColumnDeclaration(name, ae.AggregateInGroupSelect);
map.Add(ae, new ColumnExpression(ae.Type, ae.GroupByAlias, name, null));
aggColumns.Add(cd);
}
return(new SelectExpression(select.Alias, aggColumns, select.From, select.Where, select.OrderBy, select.GroupBy, select.IsDistinct, select.Skip, select.Take, select.Segment, select.IsReverse));
}
return(select);
}
private Expression MakeSubquery(Expression expression)
{
var newAlias = new TableAlias();
var aliases = DeclaredAliasGatherer.Gather(expression);
var decls = new List<ColumnDeclaration>();
foreach (var ta in aliases)
{
foreach (var col in this.columns[ta])
{
string name = decls.GetAvailableColumnName(col.Name);
var decl = new ColumnDeclaration(name, col, col.QueryType);
decls.Add(decl);
var newCol = new ColumnExpression(col.Type, col.QueryType, newAlias, col.Name);
this.map.Add(col, newCol);
}
}
return new SelectExpression(newAlias, decls, expression, null);
}
private Expression MakeSubquery(Expression expression)
{
var newAlias = new TableAlias();
var aliases = DeclaredAliasGatherer.Gather(expression);
var decls = new List <ColumnDeclaration>();
foreach (var ta in aliases)
{
foreach (var col in columns[ta])
{
var name = decls.GetAvailableColumnName(col.Name);
var decl = new ColumnDeclaration(name, col);
decls.Add(decl);
var newCol = new ColumnExpression(col.Type, newAlias, name, null);
map.Add(col, newCol);
}
}
return(new SelectExpression(newAlias, decls, expression, null));
}
protected override Expression VisitSelect(SelectExpression select)
{
_stringBuilder.Append("SELECT ");
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration column = select.Columns[i];
if (i > 0)
{
_stringBuilder.Append(", ");
}
Visit(column.Expression);
//ColumnExpression cex = Visit(column.Expression) as ColumnExpression;
//if (cex == null || cex.Name != select.Columns[i].Name)
//{
//_stringBuilder.Append(" AS ");
//_stringBuilder.Append(column.Name);
//}
}
if (select.From != null)
{
AppendNewLine(Identation.Same);
_stringBuilder.Append("FROM ");
VisitSource(select.From);
//TODO update DbExpressionTypes for handing column as table
if (select.From is ColumnExpression)
{
_stringBuilder.Append(" AS ");
_stringBuilder.Append(((ColumnExpression)select.Columns.First().Expression).Alias);
}
}
if (select.Where != null)
{
AppendNewLine(Identation.Same);
_stringBuilder.Append("WHERE ");
Visit(select.Where);
}
return(select);
}
private void GetColumns(MappingEntity entity, Dictionary <string, TableAlias> aliases, List <ColumnDeclaration> columns)
{
CompositiveMapping mapping = Mapping as CompositiveMapping;
foreach (MemberInfo mi in mapping.GetMappedMembers(entity))
{
if (!mapping.IsAssociationRelationship(entity, mi))
{
if (mapping.IsColumn(entity, mi))
{
string name = mapping.GetColumnName(entity, mi);
string aliasName = mapping.GetAlias(entity, mi);
TableAlias alias;
aliases.TryGetValue(aliasName, out alias);
var colType = this.GetColumnType(entity, mi);
string colAlias = mapping.GetColumnAlias(entity, mi);
ColumnExpression ce = new ColumnExpression(TypeHelper.GetMemberType(mi), colType, alias, name, new ColumnAlias(colAlias));
ColumnDeclaration cd = new ColumnDeclaration(name, colAlias, ce, colType);
columns.Add(cd);
}
}
}
}
private void GetColumns(MappingEntity entity, Dictionary <string, TableAlias> aliases, List <ColumnDeclaration> columns)
{
foreach (MemberInfo mi in this.mapping.GetMappedMembers(entity))
{
if (!this.mapping.IsAssociationRelationship(entity, mi))
{
if (this.mapping.IsNestedEntity(entity, mi))
{
this.GetColumns(this.mapping.GetRelatedEntity(entity, mi), aliases, columns);
}
else if (this.mapping.IsColumn(entity, mi))
{
string name = this.mapping.GetColumnName(entity, mi);
string aliasName = this.mapping.GetAlias(entity, mi);
TableAlias alias;
aliases.TryGetValue(aliasName, out alias);
var colType = this.GetColumnType(entity, mi);
ColumnExpression ce = new ColumnExpression(TypeHelper.GetMemberType(mi), colType, alias, name);
ColumnDeclaration cd = new ColumnDeclaration(name, ce, colType);
columns.Add(cd);
}
}
}
}
protected internal override Expression VisitSelect(SelectExpression select)
{
bool isFirst = sb.Length == 0;
if (!isFirst)
{
AppendNewLine(Indentation.Inner);
sb.Append('(');
}
sb.Append("SELECT ");
if (select.IsDistinct)
{
sb.Append("DISTINCT ");
}
if (select.Top != null && !this.isPostgres)
{
sb.Append("TOP (");
Visit(select.Top);
sb.Append(") ");
}
if (select.Columns.Count == 0)
{
sb.Append("0 as Dummy");
}
else
{
this.AppendNewLine(Indentation.Inner);
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration column = select.Columns[i];
AppendColumn(column);
if (i < (n - 1))
{
sb.Append(", ");
this.AppendNewLine(Indentation.Same);
}
else
{
this.Indent(Indentation.Outer);
}
}
}
if (select.From != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append("FROM ");
this.VisitSource(select.From);
}
if (select.Where != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append("WHERE ");
this.Visit(select.Where);
}
if (select.GroupBy.Count > 0)
{
this.AppendNewLine(Indentation.Same);
sb.Append("GROUP BY ");
for (int i = 0, n = select.GroupBy.Count; i < n; i++)
{
Expression exp = select.GroupBy[i];
if (i > 0)
{
sb.Append(", ");
}
this.Visit(exp);
}
}
if (select.OrderBy.Count > 0)
{
this.AppendNewLine(Indentation.Same);
sb.Append("ORDER BY ");
for (int i = 0, n = select.OrderBy.Count; i < n; i++)
{
OrderExpression exp = select.OrderBy[i];
if (i > 0)
{
sb.Append(", ");
}
this.Visit(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
sb.Append(" DESC");
}
}
}
if (select.Top != null && this.isPostgres)
{
this.AppendNewLine(Indentation.Same);
sb.Append("LIMIT ");
Visit(select.Top);
}
if (select.IsForXmlPathEmpty)
{
this.AppendNewLine(Indentation.Same);
sb.Append("FOR XML PATH('')");
}
if (!isFirst)
{
sb.Append(')');
AppendNewLine(Indentation.Outer);
}
return(select);
}
private void GetColumns(MappingEntity entity, Dictionary<string, TableAlias> aliases, List<ColumnDeclaration> columns)
{
foreach (MemberInfo mi in this.mapping.GetMappedMembers(entity))
{
if (!this.mapping.IsAssociationRelationship(entity, mi))
{
if (this.mapping.IsNestedEntity(entity, mi))
{
this.GetColumns(this.mapping.GetRelatedEntity(entity, mi), aliases, columns);
}
else if (this.mapping.IsColumn(entity, mi))
{
string name = this.mapping.GetColumnName(entity, mi);
string aliasName = this.mapping.GetAlias(entity, mi);
TableAlias alias;
aliases.TryGetValue(aliasName, out alias);
var colType = this.GetColumnType(entity, mi);
ColumnExpression ce = new ColumnExpression(TypeHelper.GetMemberType(mi), colType, alias, name);
ColumnDeclaration cd = new ColumnDeclaration(name, ce, colType);
columns.Add(cd);
}
}
}
}
protected virtual bool CompareColumnDeclaration(ColumnDeclaration a, ColumnDeclaration b)
{
return(a.Name.Equals(b.Name, StringComparison.InvariantCultureIgnoreCase) && this.Compare(a.Expression, b.Expression));
}
private SelectExpression GetCountSelectExpression(SelectExpression select)
{
BinaryExpression binaryExpression = select.Where as BinaryExpression;
if (binaryExpression != null)
{
ScalarExpression scalarExpression = binaryExpression.Left as ScalarExpression;
if (scalarExpression != null)
{
SelectExpression selectCount = scalarExpression.Select as SelectExpression;
if (selectCount != null && selectCount.Columns.Count == 1)
{
AggregateExpression aggregateExpression = (AggregateExpression)selectCount.Columns[0].Expression;
if (aggregateExpression != null && aggregateExpression.AggregateName == "Count")
{
BinaryExpression where = selectCount.Where as BinaryExpression;
if (where != null)
{
ColumnExpression columnExpression = where.Left as ColumnExpression;
if (columnExpression != null)
{
TableAlias tableAlias = new TableAlias();
TableExpression tableExpression = (TableExpression)select.From;
tableExpression = new TableExpression(tableAlias, tableExpression.Entity, tableExpression.Name);
columnExpression = new ColumnExpression(columnExpression.Type, columnExpression.QueryType, tableAlias, columnExpression.Name);
ColumnDeclaration columnDeclaration = new ColumnDeclaration(string.Empty, columnExpression, columnExpression.QueryType);
BinaryExpression where2 = Expression.MakeBinary(where.NodeType, where.Left, columnExpression);
selectCount = new SelectExpression(selectCount.Alias, selectCount.Columns, selectCount.From, where2);
List <ColumnDeclaration> columns = new List <ColumnDeclaration> {
new ColumnDeclaration("CountValue", new ScalarExpression(selectCount.Columns[0].Expression.Type, selectCount), selectCount.Columns[0].QueryType),
columnDeclaration
};
selectCount = new SelectExpression(tableAlias,
columns.ToReadOnly(),
tableExpression,
null,
selectCount.OrderBy,
null,
selectCount.IsDistinct,
selectCount.Skip,
selectCount.Take,
selectCount.IsReverse);
ColumnExpression countValueColumnExpression = new ColumnExpression(selectCount.Columns[0].Expression.Type,
selectCount.Columns[0].QueryType,
tableAlias,
"CountValue");
SelectExpression newSelect = new SelectExpression(select.Alias,
select.Columns,
select.From,
null,
select.OrderBy,
select.GroupBy,
select.IsDistinct,
select.Skip,
select.Take,
select.IsReverse);
JoinExpression joinExpression = new JoinExpression(JoinType.InnerJoin,
newSelect, selectCount,
Expression.MakeBinary(ExpressionType.Equal, columnExpression, where.Right));
select = new SelectExpression(newSelect.Alias,
newSelect.Columns,
joinExpression,
Expression.MakeBinary(binaryExpression.NodeType, countValueColumnExpression, binaryExpression.Right),
newSelect.OrderBy,
newSelect.GroupBy,
newSelect.IsDistinct,
newSelect.Skip,
newSelect.Take,
newSelect.IsReverse);
return(select);
}
}
}
}
}
}
return(null);
}
protected virtual bool CompareColumnDeclaration(ColumnDeclaration a, ColumnDeclaration b)
{
return a.Name == b.Name && this.Compare(a.Expression, b.Expression);
}
static internal ColumnExpression SingleProjection(ColumnDeclaration declaration, Alias newAlias, Type columnType)
{
return(new ColumnExpression(columnType, newAlias, declaration.Name));
}
protected override ColumnDeclaration VisitColumnDeclaration(ColumnDeclaration c)
{
var e = MakeSqlValue(Visit(c.Expression));
if (e == c.Expression)
return c;
return new ColumnDeclaration(c.Name, e);
}
protected override Expression VisitSelect(SelectExpression select)
{
sb.Append("SELECT ");
if (select.IsDistinct)
{
sb.Append("DISTINCT ");
}
if (select.Columns.Count > 0)
{
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration column = select.Columns[i];
if (i > 0)
{
sb.Append(", ");
}
ColumnExpression c = VisitValue(column.Expression) as ColumnExpression;
if (!string.IsNullOrEmpty(column.Name) && (c == null || c.Name != column.Name))
{
sb.Append(" ");
sb.Append(column.Name);
}
}
}
else
{
sb.Append("NULL ");
if (isNested)
{
sb.Append(" tmp ");
}
}
if (select.From != null)
{
AppendNewLine(Indentation.Same);
sb.Append("FROM ");
VisitSource(select.From);
}
if (select.Where != null)
{
AppendNewLine(Indentation.Same);
sb.Append("WHERE ");
VisitPredicate(select.Where);
}
if (select.GroupBy != null && select.GroupBy.Count > 0)
{
AppendNewLine(Indentation.Same);
sb.Append("GROUP BY ");
for (int i = 0, n = select.GroupBy.Count; i < n; i++)
{
if (i > 0)
{
sb.Append(", ");
}
VisitValue(select.GroupBy[i]);
}
}
if (select.OrderBy != null && select.OrderBy.Count > 0)
{
AppendNewLine(Indentation.Same);
sb.Append("ORDER BY ");
for (int i = 0, n = select.OrderBy.Count; i < n; i++)
{
OrderExpression exp = select.OrderBy[i];
if (i > 0)
{
sb.Append(", ");
}
VisitValue(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
sb.Append(" DESC");
}
}
}
int skip = select.Skip == null ? 0 : (int)select.Skip.GetConstantValue();
int take = select.Take == null ? 0 : (int)select.Take.GetConstantValue();
if (take > 0)
{
if (skip == 0)
{
AppendNewLine(Indentation.Same);
sb.AppendFormat("FETCH FIRST {0} ROWS ONLY", take);
}
else
{
AppendNewLine(Indentation.Same);
sb.Insert(0, "SELECT * FROM ( ");
sb.AppendFormat(") WHERE ROW_NUMBER() OVER() BETWEEN {0} AND {1}", skip + 1, take + skip); // "BETWEEN" is inclusive, so add 1 to Skip (BETWEEN 1 AND 3 returns 3 rows, 1, 2, and 3, and row numbers out of the DB start at 1.)
}
}
return(select);
}
protected virtual bool CompareColumnDeclaration(ColumnDeclaration a, ColumnDeclaration b)
{
return(a.Name == b.Name && Compare(a.Expression, b.Expression));
}
protected virtual ColumnDeclaration VisitColumnDeclaration(ColumnDeclaration c)
{
var e = Visit(c.Expression);
if (e == c.Expression)
return c;
return new ColumnDeclaration(c.Name, e);
}
private bool CompareColumnDeclaration(ColumnDeclaration a, ColumnDeclaration b)
{
return a.Name == b.Name && this.Compare(a.Expression, b.Expression);
}
internal void GetColumns(IEntityMapping mapping, Dictionary<string, TableAlias> aliases, List<ColumnDeclaration> columns)
{
foreach (var mi in mapping.Members)
{
if (!mi.IsRelationship && mi.IsColumn)
{
string name = mi.ColumnName;
string aliasName = mi.AliasName;
TableAlias alias;
aliases.TryGetValue(aliasName, out alias);
var colType = mi.SqlType;
ColumnExpression ce = new ColumnExpression(mi.MemberType, colType, alias, name);
ColumnDeclaration cd = new ColumnDeclaration(name, ce, colType);
columns.Add(cd);
}
}
}
private SelectExpression GetCountSelectExpression(SelectExpression select)
{
BinaryExpression binaryExpression = select.Where as BinaryExpression;
if (binaryExpression != null) {
ScalarExpression scalarExpression = binaryExpression.Left as ScalarExpression;
if (scalarExpression != null) {
SelectExpression selectCount = scalarExpression.Select as SelectExpression;
if (selectCount != null && selectCount.Columns.Count == 1) {
AggregateExpression aggregateExpression = (AggregateExpression)selectCount.Columns[0].Expression;
if (aggregateExpression != null && aggregateExpression.AggregateName == "Count") {
BinaryExpression where = selectCount.Where as BinaryExpression;
if (where != null) {
ColumnExpression columnExpression = where.Left as ColumnExpression;
if (columnExpression != null) {
TableAlias tableAlias = new TableAlias();
TableExpression tableExpression = (TableExpression)select.From;
tableExpression = new TableExpression(tableAlias, tableExpression.Entity, tableExpression.Name);
columnExpression = new ColumnExpression(columnExpression.Type, columnExpression.QueryType, tableAlias, columnExpression.Name);
ColumnDeclaration columnDeclaration = new ColumnDeclaration(string.Empty, columnExpression, columnExpression.QueryType);
BinaryExpression where2 = Expression.MakeBinary(where.NodeType, where.Left, columnExpression);
selectCount = new SelectExpression(selectCount.Alias, selectCount.Columns, selectCount.From, where2);
List<ColumnDeclaration> columns = new List<ColumnDeclaration> {
new ColumnDeclaration("CountValue", new ScalarExpression(selectCount.Columns[0].Expression.Type, selectCount), selectCount.Columns[0].QueryType),
columnDeclaration
};
selectCount = new SelectExpression(tableAlias,
columns.ToReadOnly(),
tableExpression,
null,
selectCount.OrderBy,
null,
selectCount.IsDistinct,
selectCount.Skip,
selectCount.Take,
selectCount.IsReverse);
ColumnExpression countValueColumnExpression = new ColumnExpression(selectCount.Columns[0].Expression.Type,
selectCount.Columns[0].QueryType,
tableAlias,
"CountValue");
SelectExpression newSelect = new SelectExpression(select.Alias,
select.Columns,
select.From,
null,
select.OrderBy,
select.GroupBy,
select.IsDistinct,
select.Skip,
select.Take,
select.IsReverse);
JoinExpression joinExpression = new JoinExpression(JoinType.InnerJoin,
newSelect, selectCount,
Expression.MakeBinary(ExpressionType.Equal, columnExpression, where.Right));
select = new SelectExpression(newSelect.Alias,
newSelect.Columns,
joinExpression,
Expression.MakeBinary(binaryExpression.NodeType, countValueColumnExpression, binaryExpression.Right),
newSelect.OrderBy,
newSelect.GroupBy,
newSelect.IsDistinct,
newSelect.Skip,
newSelect.Take,
newSelect.IsReverse);
return select;
}
}
}
}
}
}
return null;
}
protected override Expression VisitSelect(SelectExpression select)
{
_sb.Append("SELECT ");
if (select.IsDistinct)
{
_sb.Append("DISTINCT ");
}
for (int i = 0; i < select.Columns.Count; i++)
{
ColumnDeclaration column = select.Columns[i];
if (i > 0)
{
_sb.Append(", ");
}
ColumnExpression c = Visit(column.Expression) as ColumnExpression;
if (!(string.IsNullOrWhiteSpace(column.Name) || string.Equals(c?.Name, column.Name)))
{
_sb.Append(" AS ").Append(column.Name);
}
}
if (select.From != null)
{
AppendNewLine(Indentation.Same);
_sb.Append("FROM ");
VisitSource(select.From);
}
if (select.Where != null)
{
AppendNewLine(Indentation.Same);
_sb.Append("WHERE ");
Visit(select.Where);
}
if (select.GroupBy != null && select.GroupBy.Count > 0)
{
AppendNewLine(Indentation.Same);
_sb.Append("GROUP BY ");
for (int i = 0, n = select.GroupBy.Count; i < n; i++)
{
if (i > 0)
{
_sb.Append(", ");
}
Visit(select.GroupBy[i]);
}
}
if (select.Having != null)
{
AppendNewLine(Indentation.Same);
_sb.Append("HAVING ");
Visit(select.Having);
}
if (select.OrderBy != null && select.OrderBy.Count > 0)
{
AppendNewLine(Indentation.Same);
_sb.Append("ORDER BY ");
for (int i = 0, n = select.OrderBy.Count; i < n; i++)
{
if (i > 0)
{
_sb.Append(", ");
}
OrderExpression exp = select.OrderBy[i];
Visit(exp.Expression);
_sb.Append(exp.OrderType != OrderType.Ascending ? " DESC" : " ASC");
}
}
if (select.Limit != null)
{
AppendNewLine(Indentation.Same);
_sb.Append("LIMIT ");
Visit(select.Limit);
}
if (select.Offset != null)
{
if (select.Limit == null)
{
AppendNewLine(Indentation.Same);
_sb.Append("LIMIT -1");
}
_sb.Append(" OFFSET ");
Visit(select.Offset);
}
return(select);
}
public static SelectExpression RemoveColumn(this SelectExpression select, ColumnDeclaration column)
{
List<ColumnDeclaration> columns = new List<ColumnDeclaration>(select.Columns);
columns.Remove(column);
return select.SetColumns(columns);
}
protected override Expression VisitSelect(SelectExpression select)
{
// visit column projection first
ReadOnlyCollection<ColumnDeclaration> columns = select.Columns;
List<ColumnDeclaration> alternate = null;
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration decl = select.Columns[i];
if (select.IsDistinct || IsColumnUsed(select.Alias, decl.Name))
{
Expression expr = this.Visit(decl.Expression);
if (expr != decl.Expression)
{
decl = new ColumnDeclaration(decl.Name, expr);
}
}
else
{
decl = null; // null means it gets omitted
}
if (decl != select.Columns[i] && alternate == null)
{
alternate = new List<ColumnDeclaration>();
for (int j = 0; j < i; j++)
{
alternate.Add(select.Columns[j]);
}
}
if (decl != null && alternate != null)
{
alternate.Add(decl);
}
}
if (alternate != null)
{
columns = alternate.AsReadOnly();
}
Expression take = this.Visit(select.Take);
Expression skip = this.Visit(select.Skip);
ReadOnlyCollection<Expression> groupbys = this.VisitExpressionList(select.GroupBy);
ReadOnlyCollection<OrderExpression> orderbys = this.VisitOrderBy(select.OrderBy);
Expression where = this.Visit(select.Where);
Expression from = this.Visit(select.From);
ClearColumnsUsed(select.Alias);
if (columns != select.Columns
|| take != select.Take
|| skip != select.Skip
|| orderbys != select.OrderBy
|| groupbys != select.GroupBy
|| where != select.Where
|| from != select.From)
{
select = new SelectExpression(select.Alias, columns, from, where, orderbys, groupbys, select.IsDistinct, skip, take);
}
return select;
}
public ColumnDeclaration MapColumn(ColumnExpression ce)
{
string columnName = GetUniqueColumnName(ce.Name);
var result = new ColumnDeclaration(columnName, ce);
columns.Add(result.Name, result);
return result;
}
protected override Expression VisitSelect(SelectExpression select)
{
sb.Append("SELECT ");
if (select.IsDistinct)
{
sb.Append("DISTINCT ");
}
if (select.Columns.Count > 0)
{
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration column = select.Columns[i];
if (i > 0)
{
sb.Append(", ");
}
ColumnExpression c = VisitValue(column.Expression) as ColumnExpression;
if (!string.IsNullOrEmpty(column.Name) && (c == null || c.Name != column.Name))
{
sb.Append(" AS ");
sb.Append(column.Name);
}
}
}
else
{
sb.Append("NULL ");
if (isNested)
{
sb.Append("AS tmp ");
}
}
if (select.From != null)
{
AppendNewLine(Indentation.Same);
sb.Append("FROM ");
VisitSource(select.From);
}
if (select.Where != null)
{
AppendNewLine(Indentation.Same);
sb.Append("WHERE ");
VisitPredicate(select.Where);
}
if (select.GroupBy != null && select.GroupBy.Count > 0)
{
AppendNewLine(Indentation.Same);
sb.Append("GROUP BY ");
for (int i = 0, n = select.GroupBy.Count; i < n; i++)
{
if (i > 0)
{
sb.Append(", ");
}
VisitValue(select.GroupBy[i]);
}
}
if (select.OrderBy != null && select.OrderBy.Count > 0)
{
AppendNewLine(Indentation.Same);
sb.Append("ORDER BY ");
for (int i = 0, n = select.OrderBy.Count; i < n; i++)
{
OrderExpression exp = select.OrderBy[i];
if (i > 0)
{
sb.Append(", ");
}
VisitValue(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
sb.Append(" DESC");
}
}
}
int skip = select.Skip == null ? 0 : (int)select.Skip.GetConstantValue();
int take = select.Take == null ? 0 : (int)select.Take.GetConstantValue();
if (take > 0)
{
AppendNewLine(Indentation.Same);
sb.AppendFormat(" LIMIT {0},{1}", skip, take);
}
return(select);
}
public ColumnDeclaration NewColumn(Expression exp)
{
string columnName = GetNextColumnName();
var result = new ColumnDeclaration(columnName, exp);
columns.Add(result.Name, result);
return result;
}
protected override Expression VisitSelect(SelectExpression select)
{
// visit column projection first
var columns = select.Columns;
var wasRetained = retainAllColumns;
retainAllColumns = false;
List <ColumnDeclaration> alternate = null;
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
var decl = select.Columns[i];
if (wasRetained || select.IsDistinct || IsColumnUsed(select.Alias, decl.Name))
{
var expr = Visit(decl.Expression);
if (expr != decl.Expression)
{
decl = new ColumnDeclaration(decl.Name, expr);
}
}
else
{
decl = null; // null means it gets omitted
}
if (decl != select.Columns[i] && alternate == null)
{
alternate = new List <ColumnDeclaration>();
for (int j = 0; j < i; j++)
{
alternate.Add(select.Columns[j]);
}
}
if (decl != null && alternate != null)
{
alternate.Add(decl);
}
}
if (alternate != null)
{
columns = alternate.AsReadOnly();
}
var take = Visit(select.Take);
var skip = Visit(select.Skip);
var groupbys = VisitMemberAndExpressionList(select.GroupBy);
var orderbys = VisitOrderBy(select.OrderBy);
var where = Visit(select.Where);
var from = Visit(select.From);
ClearColumnsUsed(select.Alias);
//构成新的查询表达式
select = select.Update(from, where, orderbys, groupbys, skip, take, select.Segment, select.IsDistinct, columns, select.IsReverse);
retainAllColumns = wasRetained;
return(select);
}
static internal ColumnExpression SingleProjection(ColumnDeclaration declaration, Alias newAlias, Type columnType)
{
return new ColumnExpression(columnType, newAlias, declaration.Name);
}
protected override Expression VisitSelect(SelectExpression select)
{
// visit column projection first
ReadOnlyCollection <ColumnDeclaration> columns = select.Columns;
List <ColumnDeclaration> alternate = null;
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration decl = select.Columns[i];
if (select.IsDistinct || IsColumnUsed(select.Alias, decl.Name))
{
Expression expr = this.Visit(decl.Expression);
if (expr != decl.Expression)
{
decl = new ColumnDeclaration(decl.Name, expr);
}
}
else
{
decl = null; // null means it gets omitted
}
if (decl != select.Columns[i] && alternate == null)
{
alternate = new List <ColumnDeclaration>();
for (int j = 0; j < i; j++)
{
alternate.Add(select.Columns[j]);
}
}
if (decl != null && alternate != null)
{
alternate.Add(decl);
}
}
if (alternate != null)
{
columns = alternate.AsReadOnly();
}
Expression take = this.Visit(select.Take);
Expression skip = this.Visit(select.Skip);
ReadOnlyCollection <Expression> groupbys = this.VisitExpressionList(select.GroupBy);
ReadOnlyCollection <OrderExpression> orderbys = this.VisitOrderBy(select.OrderBy);
Expression where = this.Visit(select.Where);
Expression from = this.Visit(select.From);
ClearColumnsUsed(select.Alias);
if (columns != select.Columns ||
take != select.Take ||
skip != select.Skip ||
orderbys != select.OrderBy ||
groupbys != select.GroupBy ||
where != select.Where ||
from != select.From)
{
select = new SelectExpression(select.Alias, columns, from, where, orderbys, groupbys, select.IsDistinct, skip, take);
}
return(select);
}
private void AppendColumn(ColumnDeclaration column)
{
ColumnExpression c = this.Visit(column.Expression) as ColumnExpression;
if (column.Name.HasText() && (c == null || c.Name != column.Name))
{
sb.Append(" AS ");
sb.Append(column.Name.SqlEscape());
}
}
