void MoveCountSubQuery(IQueryElement element)
{
if (element.ElementType != QueryElementType.SqlQuery)
return;
var query = (SelectQuery)element;
for (var i = 0; i < query.Select.Columns.Count; i++)
{
var col = query.Select.Columns[i];
// The column is a subquery.
//
if (col.Expression.ElementType == QueryElementType.SqlQuery)
{
var subQuery = (SelectQuery)col.Expression;
var isCount = false;
// Check if subquery is Count subquery.
//
if (subQuery.Select.Columns.Count == 1)
{
var subCol = subQuery.Select.Columns[0];
if (subCol.Expression.ElementType == QueryElementType.SqlFunction)
isCount = ((SqlFunction)subCol.Expression).Name == "Count";
}
if (!isCount)
continue;
// Check if subquery where clause does not have ORs.
//
SelectQueryOptimizer.OptimizeSearchCondition(subQuery.Where.SearchCondition);
var allAnd = true;
for (var j = 0; allAnd && j < subQuery.Where.SearchCondition.Conditions.Count - 1; j++)
{
var cond = subQuery.Where.SearchCondition.Conditions[j];
if (cond.IsOr)
allAnd = false;
}
if (!allAnd || !ConvertCountSubQuery(subQuery))
continue;
// Collect tables.
//
var allTables = new HashSet<ISqlTableSource>();
var levelTables = new HashSet<ISqlTableSource>();
new QueryVisitor().Visit(subQuery, e =>
{
if (e is ISqlTableSource source)
allTables.Add(source);
});
new QueryVisitor().Visit(subQuery, e =>
{
if (e is ISqlTableSource source)
if (subQuery.From.IsChild(source))
levelTables.Add(source);
});
bool CheckTable(IQueryElement e)
{
switch (e.ElementType)
{
case QueryElementType.SqlField : return !allTables.Contains(((SqlField) e).Table);
case QueryElementType.Column : return !allTables.Contains(((SqlColumn)e).Parent);
}
return false;
}
var join = subQuery.LeftJoin();
query.From.Tables[0].Joins.Add(join.JoinedTable);
for (var j = 0; j < subQuery.Where.SearchCondition.Conditions.Count; j++)
{
var cond = subQuery.Where.SearchCondition.Conditions[j];
if (QueryVisitor.Find(cond, CheckTable) == null)
continue;
var replaced = new Dictionary<IQueryElement,IQueryElement>();
var nc = new QueryVisitor().Convert(cond, e =>
{
var ne = e;
switch (e.ElementType)
{
case QueryElementType.SqlField :
if (replaced.TryGetValue(e, out ne))
return ne;
if (levelTables.Contains(((SqlField)e).Table))
{
subQuery.GroupBy.Expr((SqlField)e);
ne = subQuery.Select.Columns[subQuery.Select.Add((SqlField)e)];
}
break;
case QueryElementType.Column :
if (replaced.TryGetValue(e, out ne))
return ne;
if (levelTables.Contains(((SqlColumn)e).Parent))
{
subQuery.GroupBy.Expr((SqlColumn)e);
ne = subQuery.Select.Columns[subQuery.Select.Add((SqlColumn)e)];
}
break;
}
if (!ReferenceEquals(e, ne))
replaced.Add(e, ne);
return ne;
});
if (nc != null && !ReferenceEquals(nc, cond))
{
join.JoinedTable.Condition.Conditions.Add(nc);
subQuery.Where.SearchCondition.Conditions.RemoveAt(j);
j--;
}
}
if (!query.GroupBy.IsEmpty/* && subQuery.Select.Columns.Count > 1*/)
{
var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression;
subQuery.Select.Columns.RemoveAt(0);
query.Select.Columns[i].Expression =
new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[0]);
}
else
{
query.Select.Columns[i].Expression = subQuery.Select.Columns[0];
}
}
}
}
SelectQuery MoveSubQueryColumn(SelectQuery selectQuery)
{
var dic = new Dictionary<IQueryElement,IQueryElement>();
new QueryVisitor().Visit(selectQuery, element =>
{
if (element.ElementType != QueryElementType.SqlQuery)
return;
var query = (SelectQuery)element;
for (var i = 0; i < query.Select.Columns.Count; i++)
{
var col = query.Select.Columns[i];
if (col.Expression.ElementType == QueryElementType.SqlQuery)
{
var subQuery = (SelectQuery)col.Expression;
var allTables = new HashSet<ISqlTableSource>();
var levelTables = new HashSet<ISqlTableSource>();
bool CheckTable(IQueryElement e)
{
switch (e.ElementType)
{
case QueryElementType.SqlField : return !allTables.Contains(((SqlField) e).Table);
case QueryElementType.Column : return !allTables.Contains(((SqlColumn)e).Parent);
}
return false;
}
new QueryVisitor().Visit(subQuery, e =>
{
if (e is ISqlTableSource source)
allTables.Add(source);
});
new QueryVisitor().Visit(subQuery, e =>
{
if (e is ISqlTableSource source && subQuery.From.IsChild(source))
levelTables.Add(source);
});
if (SqlProviderFlags.IsSubQueryColumnSupported && QueryVisitor.Find(subQuery, CheckTable) == null)
continue;
// Join should not have ParentSelect, while SubQuery has
subQuery.ParentSelect = null;
var join = subQuery.LeftJoin();
query.From.Tables[0].Joins.Add(join.JoinedTable);
SelectQueryOptimizer.OptimizeSearchCondition(subQuery.Where.SearchCondition);
var isCount = false;
var isAggregated = false;
if (subQuery.Select.Columns.Count == 1)
{
var subCol = subQuery.Select.Columns[0];
if (subCol.Expression.ElementType == QueryElementType.SqlFunction)
{
switch (((SqlFunction)subCol.Expression).Name)
{
case "Count" : isCount = true; break;
}
isAggregated = ((SqlFunction) subCol.Expression).IsAggregate;
}
}
if (SqlProviderFlags.IsSubQueryColumnSupported && !isCount)
continue;
var allAnd = true;
for (var j = 0; allAnd && j < subQuery.Where.SearchCondition.Conditions.Count - 1; j++)
{
var cond = subQuery.Where.SearchCondition.Conditions[j];
if (cond.IsOr)
allAnd = false;
}
if (!allAnd)
continue;
var modified = false;
for (var j = 0; j < subQuery.Where.SearchCondition.Conditions.Count; j++)
{
var cond = subQuery.Where.SearchCondition.Conditions[j];
if (QueryVisitor.Find(cond, CheckTable) == null)
continue;
var replaced = new Dictionary<IQueryElement,IQueryElement>();
var nc = new QueryVisitor().Convert(cond, e =>
{
var ne = e;
switch (e.ElementType)
{
case QueryElementType.SqlField :
if (replaced.TryGetValue(e, out ne))
return ne;
if (levelTables.Contains(((SqlField)e).Table))
{
if (isAggregated)
subQuery.GroupBy.Expr((SqlField)e);
ne = subQuery.Select.Columns[subQuery.Select.Add((SqlField)e)];
}
break;
case QueryElementType.Column :
if (replaced.TryGetValue(e, out ne))
return ne;
if (levelTables.Contains(((SqlColumn)e).Parent))
{
if (isAggregated)
subQuery.GroupBy.Expr((SqlColumn)e);
ne = subQuery.Select.Columns[subQuery.Select.Add((SqlColumn)e)];
}
break;
}
if (!ReferenceEquals(e, ne))
replaced.Add(e, ne);
return ne;
});
if (nc != null && !ReferenceEquals(nc, cond))
{
modified = true;
join.JoinedTable.Condition.Conditions.Add(nc);
subQuery.Where.SearchCondition.Conditions.RemoveAt(j);
j--;
}
}
if (modified || isAggregated)
{
SqlColumn newColumn;
if (isCount && !query.GroupBy.IsEmpty)
{
var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression;
subQuery.Select.Columns.RemoveAt(0);
newColumn = new SqlColumn(
query,
new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[0]));
}
else if (isAggregated && !query.GroupBy.IsEmpty)
{
var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression;
subQuery.Select.Columns.RemoveAt(0);
var idx = subQuery.Select.Add(oldFunc.Parameters[0]);
newColumn = new SqlColumn(
query,
new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[idx]));
}
else
{
newColumn = new SqlColumn(query, subQuery.Select.Columns[0]);
}
dic.Add(col, newColumn);
}
}