public static ISqlExpression ConvertToSubquery(this SelectQuery sql, ISqlExpression element, HashSet <SqlTable> tableSet,
List <SqlTable> tables,
SqlTable firstTable,
SqlTable secondTable)
{
// for table field references from association tables we must rewrite them with subquery
if (element.ElementType == QueryElementType.SqlField)
{
var fld = (SqlField)element;
var tbl = (SqlTable)fld.Table;
// table is an association table, used in FROM clause - generate subquery
if (tbl != firstTable && (secondTable == null || tbl != secondTable) && tableSet.Contains(tbl))
{
var tempCopy = sql.Clone();
var tempTables = new List <SqlTableSource>();
// create copy of tables from main FROM clause for subquery clause
new QueryVisitor().Visit(tempCopy.From, ee => {
if (ee.ElementType == QueryElementType.TableSource)
{
tempTables.Add((SqlTableSource)ee);
}
});
// main table reference in subquery
var tt = tempTables[tables.IndexOf(tbl)];
tempCopy.Select.Columns.Clear();
tempCopy.Select.Add(((SqlTable)tt.Source).Fields.FirstOrDefault(x => x.Name == fld.Name));
// create new WHERE for subquery
tempCopy.Where.SearchCondition.Conditions.Clear();
var firstTableKeys = tempCopy.From.Tables[0].Source.GetKeys(true);
foreach (SqlField key in firstTableKeys)
{
tempCopy.Where.Field(key).Equal.Field(firstTable.Fields.FirstOrDefault(x => x.Name == key.Name));
}
if (secondTable != null)
{
var secondTableKeys = tempCopy.From.Tables[0].Joins[0].Table.Source.GetKeys(true);
foreach (SqlField key in secondTableKeys)
{
tempCopy.Where.Field(key).Equal.Field(secondTable.Fields.FirstOrDefault(x => x.Name == key.Name));
}
}
// set main query as parent
tempCopy.ParentSelect = sql;
return(tempCopy);
}
}
return(element);
}
public override ICloneableElement Clone(Dictionary <ICloneableElement, ICloneableElement> objectTree, Predicate <ICloneableElement> doClone)
{
var clone = new SqlSelectStatement();
if (SelectQuery != null)
{
clone.SelectQuery = (SelectQuery)SelectQuery.Clone(objectTree, doClone);
}
clone.Parameters.AddRange(Parameters.Select(p => (SqlParameter)p.Clone(objectTree, doClone)));
objectTree.Add(this, clone);
return(clone);
}
public override ICloneableElement Clone(Dictionary <ICloneableElement, ICloneableElement> objectTree, Predicate <ICloneableElement> doClone)
{
var clone = new SqlSelectStatement();
if (SelectQuery != null)
{
clone.SelectQuery = (SelectQuery)SelectQuery.Clone(objectTree, doClone);
}
if (With != null)
{
clone.With = (SqlWithClause)With.Clone(objectTree, doClone);
}
objectTree.Add(this, clone);
return(clone);
}