void RemoveSource(SelectQuery.TableSource fromTable, SelectQuery.JoinedTable join)
{
if (_removedSources == null)
{
_removedSources = new HashSet <int>();
}
_removedSources.Add(join.Table.SourceID);
if (_equalityMap != null)
{
var keys = _equalityMap.Keys.Where(k => k.SourceID == join.Table.SourceID).ToArray();
foreach (var key in keys)
{
var newField = MapToSource(fromTable, key, fromTable.SourceID);
if (newField != null)
{
ReplaceField(key, newField);
}
_equalityMap.Remove(key);
}
}
ResetFieldSearchCache(join.Table);
}
List <FoundEquality> SearchForFields(SelectQuery.TableSource manySource, SelectQuery.JoinedTable join)
{
var key = Tuple.Create(manySource, join.Table);
List <FoundEquality> found = null;
if (_fieldPairCache != null && _fieldPairCache.TryGetValue(key, out found))
{
return(found);
}
for (var i1 = 0; i1 < join.Condition.Conditions.Count; i1++)
{
var c = join.Condition.Conditions[i1];
if (c.IsOr)
{
found = null;
break;
}
if (c.ElementType != QueryElementType.Condition ||
c.Predicate.ElementType != QueryElementType.ExprExprPredicate ||
((SelectQuery.Predicate.ExprExpr)c.Predicate).Operator != SelectQuery.Predicate.Operator.Equal)
{
continue;
}
var predicate = (SelectQuery.Predicate.ExprExpr)c.Predicate;
var equality = new FoundEquality();
if (!MatchFields(manySource, join.Table,
GetUnderlayingField(predicate.Expr1),
GetUnderlayingField(predicate.Expr2),
equality))
{
continue;
}
equality.OneCondition = c;
if (found == null)
{
found = new List <FoundEquality>();
}
found.Add(equality);
}
if (_fieldPairCache == null)
{
_fieldPairCache = new Dictionary <Tuple <SelectQuery.TableSource, SelectQuery.TableSource>, List <FoundEquality> >();
}
_fieldPairCache.Add(key, found);
return(found);
}
bool MatchFields(SelectQuery.TableSource manySource, SelectQuery.TableSource oneSource, VirtualField field1, VirtualField field2, FoundEquality equality)
{
if (field1 == null || field2 == null)
{
return(false);
}
DetectField(manySource, oneSource, field1, equality);
DetectField(manySource, oneSource, field2, equality);
return(equality.OneField != null && equality.ManyField != null);
}
void ReplaceSource(SelectQuery.TableSource fromTable, SelectQuery.JoinedTable oldSource, SelectQuery.TableSource newSource)
{
var oldFields = GetFields(oldSource.Table.Source);
var newFields = GetFields(newSource.Source);
foreach (var old in oldFields)
{
var newField = newFields[old.Key];
ReplaceField(old.Value, newField);
}
RemoveSource(fromTable, oldSource);
}
void ResetFieldSearchCache(SelectQuery.TableSource table)
{
if (_fieldPairCache == null)
{
return;
}
var keys = _fieldPairCache.Keys.Where(k => k.Item2 == table || k.Item1 == table).ToArray();
foreach (var key in keys)
{
_fieldPairCache.Remove(key);
}
}
SelectQuery.TableSource OptimizeSubQuery(
SelectQuery.TableSource source,
bool optimizeWhere,
bool allColumns,
bool isApplySupported,
bool optimizeValues,
bool optimizeColumns)
{
foreach (var jt in source.Joins)
{
var table = OptimizeSubQuery(
jt.Table,
jt.JoinType == SelectQuery.JoinType.Inner || jt.JoinType == SelectQuery.JoinType.CrossApply,
false,
isApplySupported,
jt.JoinType == SelectQuery.JoinType.Inner || jt.JoinType == SelectQuery.JoinType.CrossApply,
optimizeColumns);
if (table != jt.Table)
{
var sql = jt.Table.Source as SelectQuery;
if (sql != null && sql.OrderBy.Items.Count > 0)
{
foreach (var item in sql.OrderBy.Items)
{
_selectQuery.OrderBy.Expr(item.Expression, item.IsDescending);
}
}
jt.Table = table;
}
}
var select = source.Source as SelectQuery;
if (select != null)
{
var canRemove = !CorrectCrossJoinQuery(select);
if (canRemove)
{
return(RemoveSubQuery(source, optimizeWhere, allColumns && !isApplySupported, optimizeValues, optimizeColumns));
}
}
return(source);
}
static IEnumerable <T> QueryTable <T>(IDataContext dataContext)
{
var query = new SelectQuery();
var table = new SqlTable(typeof(T));
var tableSource = new SelectQuery.TableSource(table, "t");
query.From.Tables.Add(tableSource);
var connection = (DataConnection)dataContext;
var sqlBuilder = connection.DataProvider.CreateSqlBuilder();
var sb = new StringBuilder();
sqlBuilder.BuildSql(0, query, sb);
return(connection.Query <T>(sb.ToString()));
}
void DetectField(SelectQuery.TableSource manySource, SelectQuery.TableSource oneSource, VirtualField field, FoundEquality equality)
{
field = GetNewField(field);
if (oneSource.Source.SourceID == field.SourceID)
{
equality.OneField = field;
}
else if (manySource.Source.SourceID == field.SourceID)
{
equality.ManyField = field;
}
else
{
equality.ManyField = MapToSource(manySource, field, manySource.Source.SourceID);
}
}
static SelectQuery.TableSource FindField(SqlField field, SelectQuery.TableSource table)
{
if (field.Table == table.Source)
{
return(table);
}
foreach (var @join in table.Joins)
{
var t = FindField(field, @join.Table);
if (t != null)
{
return(@join.Table);
}
}
return(null);
}
VirtualField MapToSourceInternal(SelectQuery.TableSource fromTable, VirtualField field, int sourceId, HashSet <VirtualField> visited)
{
if (visited.Contains(field))
{
return(null);
}
if (field.SourceID == sourceId)
{
return(field);
}
visited.Add(field);
if (_equalityMap == null)
{
return(null);
}
var sourceIndex = GetSourceIndex(fromTable, sourceId);
HashSet <Tuple <int, VirtualField> > sameFields;
if (_equalityMap.TryGetValue(field, out sameFields))
{
foreach (var pair in sameFields)
{
var itemIndex = GetSourceIndex(fromTable, pair.Item1);
if (itemIndex >= 0 && (sourceIndex == 0 || itemIndex < sourceIndex))
{
var newField = MapToSourceInternal(fromTable, pair.Item2, sourceId, visited);
if (newField != null)
{
return(newField);
}
}
}
}
return(null);
}
bool IsDependedBetweenJoins(SelectQuery.TableSource table,
SelectQuery.JoinedTable testedJoin)
{
var testedSources = new HashSet <int>(testedJoin.Table.GetTables().Select(t => t.SourceID));
foreach (var tableJoin in table.Joins)
{
if (testedSources.Contains(tableJoin.Table.SourceID))
{
continue;
}
if (IsDependedOnJoin(table, tableJoin, testedSources))
{
return(true);
}
}
return(IsDependedExcludeJoins(testedSources));
}
bool CanWeReplaceFieldInternal(SelectQuery.TableSource table, VirtualField field, HashSet <int> excludeSourceIds,
int testedSourceIndex, HashSet <VirtualField> visited)
{
if (visited.Contains(field))
{
return(false);
}
if (!excludeSourceIds.Contains(field.SourceID) && !IsSourceRemoved(field.SourceID))
{
return(true);
}
visited.Add(field);
if (_equalityMap == null)
{
return(false);
}
if (testedSourceIndex < 0)
{
return(false);
}
HashSet <Tuple <int, VirtualField> > sameFields;
if (_equalityMap.TryGetValue(field, out sameFields))
{
foreach (var pair in sameFields)
{
if ((testedSourceIndex == 0 || GetSourceIndex(table, pair.Item1) > testedSourceIndex) &&
CanWeReplaceFieldInternal(table, pair.Item2, excludeSourceIds, testedSourceIndex, visited))
{
return(true);
}
}
}
return(false);
}
int GetSourceIndex(SelectQuery.TableSource table, int sourceId)
{
if (table == null || table.SourceID == sourceId || sourceId == -1)
{
return(0);
}
var i = 0;
while (i < table.Joins.Count)
{
if (table.Joins[i].Table.SourceID == sourceId)
{
return(i + 1);
}
++i;
}
return(-1);
}
void FlattenJoins(SelectQuery.TableSource table)
{
for (var i = 0; i < table.Joins.Count; i++)
{
var j = table.Joins[i];
FlattenJoins(j.Table);
if (j.JoinType == SelectQuery.JoinType.Inner)
{
for (var si = 0; si < j.Table.Joins.Count; si++)
{
var sj = j.Table.Joins[si];
if ((sj.JoinType == SelectQuery.JoinType.Inner || sj.JoinType == SelectQuery.JoinType.Left) &&
table != j.Table && !HasDependencyWithParent(j, sj))
{
table.Joins.Add(sj);
j.Table.Joins.RemoveAt(si);
--si;
}
}
}
}
}
bool IsDependedOnJoin(SelectQuery.TableSource table, SelectQuery.JoinedTable testedJoin, HashSet <int> testedSources)
{
var dependent = false;
var currentSourceId = testedJoin.Table.SourceID;
// check everyting that can be dependent on specific table
new QueryVisitor().VisitParentFirst(testedJoin, e =>
{
if (dependent)
{
return(false);
}
var expression = e as ISqlExpression;
if (expression != null)
{
var field = GetUnderlayingField(expression);
if (field != null)
{
var newField = GetNewField(field);
var local = testedSources.Contains(newField.SourceID);
if (local)
{
dependent = !CanWeReplaceField(table, newField, testedSources, currentSourceId);
}
}
}
return(!dependent);
});
return(dependent);
}
void OptimizeApply(SelectQuery.TableSource tableSource, SelectQuery.JoinedTable joinTable, bool isApplySupported, bool optimizeColumns)
{
var joinSource = joinTable.Table;
foreach (var join in joinSource.Joins)
{
if (join.JoinType == SelectQuery.JoinType.CrossApply || join.JoinType == SelectQuery.JoinType.OuterApply)
{
OptimizeApply(joinSource, join, isApplySupported, optimizeColumns);
}
}
if (isApplySupported && !joinTable.CanConvertApply)
{
return;
}
if (joinSource.Source.ElementType == QueryElementType.SqlQuery)
{
var sql = (SelectQuery)joinSource.Source;
var isAgg = sql.Select.Columns.Any(c => IsAggregationFunction(c.Expression));
if (isApplySupported && (isAgg || sql.Select.HasModifier))
{
return;
}
var searchCondition = new List <SelectQuery.Condition>(sql.Where.SearchCondition.Conditions);
sql.Where.SearchCondition.Conditions.Clear();
if (!ContainsTable(tableSource.Source, sql))
{
if (!(joinTable.JoinType == SelectQuery.JoinType.CrossApply && searchCondition.Count == 0) && // CROSS JOIN
sql.Select.HasModifier)
{
throw new LinqToDBException("Database do not support CROSS/OUTER APPLY join required by the query.");
}
joinTable.JoinType = joinTable.JoinType == SelectQuery.JoinType.CrossApply ? SelectQuery.JoinType.Inner : SelectQuery.JoinType.Left;
joinTable.Condition.Conditions.AddRange(searchCondition);
}
else
{
sql.Where.SearchCondition.Conditions.AddRange(searchCondition);
var table = OptimizeSubQuery(
joinTable.Table,
joinTable.JoinType == SelectQuery.JoinType.Inner || joinTable.JoinType == SelectQuery.JoinType.CrossApply,
joinTable.JoinType == SelectQuery.JoinType.CrossApply,
isApplySupported,
joinTable.JoinType == SelectQuery.JoinType.Inner || joinTable.JoinType == SelectQuery.JoinType.CrossApply,
optimizeColumns);
if (table != joinTable.Table)
{
var q = joinTable.Table.Source as SelectQuery;
if (q != null && q.OrderBy.Items.Count > 0)
{
foreach (var item in q.OrderBy.Items)
{
_selectQuery.OrderBy.Expr(item.Expression, item.IsDescending);
}
}
joinTable.Table = table;
OptimizeApply(tableSource, joinTable, isApplySupported, optimizeColumns);
}
}
}
else
{
if (!ContainsTable(tableSource.Source, joinSource.Source))
{
joinTable.JoinType = joinTable.JoinType == SelectQuery.JoinType.CrossApply ? SelectQuery.JoinType.Inner : SelectQuery.JoinType.Left;
}
}
}
bool TryMergeWithTable(SelectQuery.TableSource fromTable, SelectQuery.JoinedTable join, List <List <string> > uniqueKeys)
{
if (join.Table.Joins.Count != 0)
{
return(false);
}
var hasLeftJoin = join.JoinType == SelectQuery.JoinType.Left;
var found = SearchForFields(fromTable, join);
if (found == null)
{
return(false);
}
if (hasLeftJoin)
{
if (join.Condition.Conditions.Count != found.Count)
{
return(false);
}
// currently no dependecies in search condition allowed for left join
if (IsDependedExcludeJoins(join))
{
return(false);
}
}
HashSet <string> foundFields = new HashSet <string>(found.Select(f => f.OneField.Name));
HashSet <string> uniqueFields = null;
for (var i = 0; i < uniqueKeys.Count; i++)
{
var keys = uniqueKeys[i];
if (keys.All(k => foundFields.Contains(k)))
{
if (uniqueFields == null)
{
uniqueFields = new HashSet <string>();
}
foreach (var key in keys)
{
uniqueFields.Add(key);
}
}
}
if (uniqueFields != null)
{
foreach (var item in found)
{
if (uniqueFields.Contains(item.OneField.Name))
{
// remove unique key conditions
join.Condition.Conditions.Remove(item.OneCondition);
AddEqualFields(item.ManyField, item.OneField, fromTable.SourceID);
}
}
// move rest conditions to the Where section
if (join.Condition.Conditions.Count > 0)
{
AddSearchConditions(_selectQuery.Where.SearchCondition, join.Condition.Conditions);
join.Condition.Conditions.Clear();
}
// add check that previously joined fields is not null
foreach (var item in found)
{
if (item.ManyField.CanBeNull)
{
var newField = MapToSource(fromTable, item.ManyField, fromTable.SourceID);
AddSearchCondition(_selectQuery.Where.SearchCondition,
new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(newField.Element, true)));
}
}
// add mapping to new source
ReplaceSource(fromTable, join, fromTable);
return(true);
}
return(false);
}
bool TryMergeJoins(SelectQuery.TableSource fromTable,
SelectQuery.TableSource manySource,
SelectQuery.JoinedTable join1, SelectQuery.JoinedTable join2,
List <List <string> > uniqueKeys)
{
var found1 = SearchForFields(manySource, join1);
if (found1 == null)
{
return(false);
}
var found2 = SearchForFields(manySource, join2);
if (found2 == null)
{
return(false);
}
var hasLeftJoin = join1.JoinType == SelectQuery.JoinType.Left || join2.JoinType == SelectQuery.JoinType.Left;
// left join should match exactly
if (hasLeftJoin)
{
if (join1.Condition.Conditions.Count != join2.Condition.Conditions.Count)
{
return(false);
}
if (found1.Count != found2.Count)
{
return(false);
}
if (join1.Table.Joins.Count != 0 || join2.Table.Joins.Count != 0)
{
return(false);
}
}
List <FoundEquality> found = null;
for (var i1 = 0; i1 < found1.Count; i1++)
{
var f1 = found1[i1];
for (var i2 = 0; i2 < found2.Count; i2++)
{
var f2 = found2[i2];
if (f1.ManyField.Name == f2.ManyField.Name && f1.OneField.Name == f2.OneField.Name)
{
if (found == null)
{
found = new List <FoundEquality>();
}
found.Add(f2);
}
}
}
if (found == null)
{
return(false);
}
if (hasLeftJoin)
{
// for left join each expression should be used
if (found.Count != join1.Condition.Conditions.Count)
{
return(false);
}
// currently no dependecies in search condition allowed for left join
if (IsDepended(join1, join2))
{
return(false);
}
}
HashSet <string> foundFields = new HashSet <string>(found.Select(f => f.OneField.Name));
HashSet <string> uniqueFields = null;
for (var i = 0; i < uniqueKeys.Count; i++)
{
var keys = uniqueKeys[i];
if (keys.All(k => foundFields.Contains(k)))
{
if (uniqueFields == null)
{
uniqueFields = new HashSet <string>();
}
foreach (var key in keys)
{
uniqueFields.Add(key);
}
}
}
if (uniqueFields != null)
{
foreach (var item in found)
{
if (uniqueFields.Contains(item.OneField.Name))
{
// remove from second
join2.Condition.Conditions.Remove(item.OneCondition);
AddEqualFields(item.ManyField, item.OneField, fromTable.SourceID);
}
}
// move rest conditions to first
if (join2.Condition.Conditions.Count > 0)
{
AddSearchConditions(join1.Condition, join2.Condition.Conditions);
join2.Condition.Conditions.Clear();
}
join1.Table.Joins.AddRange(join2.Table.Joins);
// add mapping to new source
ReplaceSource(fromTable, join2, join1.Table);
return(true);
}
return(false);
}
// here we can deal with LEFT JOIN and INNER JOIN
bool TryToRemoveIndepended(SelectQuery.TableSource fromTable, SelectQuery.TableSource manySource,
SelectQuery.JoinedTable join,
List <List <string> > uniqueKeys)
{
if (join.JoinType == SelectQuery.JoinType.Inner)
{
return(false);
}
var found = SearchForFields(manySource, join);
if (found == null)
{
return(false);
}
HashSet <string> foundFields = new HashSet <string>(found.Select(f => f.OneField.Name));
HashSet <string> uniqueFields = null;
for (var i = 0; i < uniqueKeys.Count; i++)
{
var keys = uniqueKeys[i];
if (keys.All(k => foundFields.Contains(k)))
{
if (uniqueFields == null)
{
uniqueFields = new HashSet <string>();
}
foreach (var key in keys)
{
uniqueFields.Add(key);
}
}
}
if (uniqueFields != null)
{
if (join.JoinType == SelectQuery.JoinType.Inner)
{
foreach (var item in found)
{
if (uniqueFields.Contains(item.OneField.Name))
{
// remove from second
join.Condition.Conditions.Remove(item.OneCondition);
AddEqualFields(item.ManyField, item.OneField, fromTable.SourceID);
}
}
// move rest conditions to Where
if (join.Condition.Conditions.Count > 0)
{
AddSearchConditions(_selectQuery.Where.SearchCondition, join.Condition.Conditions);
join.Condition.Conditions.Clear();
}
// add filer for nullable fileds because after INNER JOIN records with nulls dissapear
foreach (var item in found)
{
if (item.ManyField.CanBeNull)
{
AddSearchCondition(_selectQuery.Where.SearchCondition,
new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(item.ManyField.Element, true)));
}
}
}
RemoveSource(fromTable, join);
return(true);
}
return(false);
}
bool CorrectCrossJoinQuery(SelectQuery query)
{
var select = query.Select;
if (select.From.Tables.Count == 1)
{
return(false);
}
var joins = select.From.Tables.SelectMany(_ => _.Joins).Distinct().ToArray();
if (joins.Length == 0)
{
return(false);
}
var tables = select.From.Tables.ToArray();
foreach (var t in tables)
{
t.Joins.Clear();
}
var baseTable = tables[0];
if (_flags.IsCrossJoinSupported || _flags.IsInnerJoinAsCrossSupported)
{
select.From.Tables.Clear();
select.From.Tables.Add(baseTable);
foreach (var t in tables.Skip(1))
{
baseTable.Joins.Add(new SelectQuery.JoinedTable(SelectQuery.JoinType.Inner, t, false));
}
foreach (var j in joins)
{
baseTable.Joins.Add(j);
}
}
else
{
// move to subquery
var subQuery = new SelectQuery();
subQuery.Select.From.Tables.AddRange(tables);
baseTable = new SelectQuery.TableSource(subQuery, "cross");
baseTable.Joins.AddRange(joins);
query.Select.From.Tables.Clear();
var sources = new HashSet <ISqlTableSource>(tables.Select(t => t.Source));
var foundFields = new HashSet <ISqlExpression>();
QueryHelper.CollectDependencies(query.RootQuery(), sources, foundFields);
QueryHelper.CollectDependencies(baseTable, sources, foundFields);
var toReplace = foundFields.ToDictionary(f => f,
f => subQuery.Select.Columns[subQuery.Select.Add(f)] as ISqlExpression);
Func <ISqlExpression, ISqlExpression> transformFunc = e =>
{
ISqlExpression newValue;
return(toReplace.TryGetValue(e, out newValue) ? newValue : e);
};
((ISqlExpressionWalkable)query.RootQuery()).Walk(false, transformFunc);
foreach (var j in joins)
{
((ISqlExpressionWalkable)j).Walk(false, transformFunc);
}
query.Select.From.Tables.Add(baseTable);
}
return(true);
}
bool CanWeReplaceField(SelectQuery.TableSource table, VirtualField field, HashSet <int> excludeSourceId, int testedSourceId)
{
var visited = new HashSet <VirtualField>();
return(CanWeReplaceFieldInternal(table, field, excludeSourceId, GetSourceIndex(table, testedSourceId), visited));
}
SelectQuery.TableSource RemoveSubQuery(
SelectQuery.TableSource childSource,
bool concatWhere,
bool allColumns,
bool optimizeValues,
bool optimizeColumns)
{
var query = (SelectQuery)childSource.Source;
var isQueryOK = query.From.Tables.Count == 1;
isQueryOK = isQueryOK && (concatWhere || query.Where.IsEmpty && query.Having.IsEmpty);
isQueryOK = isQueryOK && !query.HasUnion && query.GroupBy.IsEmpty && !query.Select.HasModifier;
//isQueryOK = isQueryOK && (_flags.IsDistinctOrderBySupported || query.Select.IsDistinct );
if (!isQueryOK)
{
return(childSource);
}
var isColumnsOK =
(allColumns && !query.Select.Columns.Any(c => IsAggregationFunction(c.Expression))) ||
!query.Select.Columns.Any(c => CheckColumn(c, c.Expression, query, optimizeValues, optimizeColumns));
if (!isColumnsOK)
{
return(childSource);
}
var map = new Dictionary <ISqlExpression, ISqlExpression>(query.Select.Columns.Count);
foreach (var c in query.Select.Columns)
{
map.Add(c, c.Expression);
}
var top = _selectQuery;
while (top.ParentSelect != null)
{
top = top.ParentSelect;
}
((ISqlExpressionWalkable)top).Walk(false, expr =>
{
ISqlExpression fld;
return(map.TryGetValue(expr, out fld) ? fld : expr);
});
new QueryVisitor().Visit(top, expr =>
{
if (expr.ElementType == QueryElementType.InListPredicate)
{
var p = (SelectQuery.Predicate.InList)expr;
if (p.Expr1 == query)
{
p.Expr1 = query.From.Tables[0];
}
}
});
query.From.Tables[0].Joins.AddRange(childSource.Joins);
if (query.From.Tables[0].Alias == null)
{
query.From.Tables[0].Alias = childSource.Alias;
}
if (!query.Where.IsEmpty)
{
ConcatSearchCondition(_selectQuery.Where, query.Where);
}
if (!query.Having.IsEmpty)
{
ConcatSearchCondition(_selectQuery.Having, query.Having);
}
((ISqlExpressionWalkable)top).Walk(false, expr =>
{
if (expr is SelectQuery)
{
var sql = (SelectQuery)expr;
if (sql.ParentSelect == query)
{
sql.ParentSelect = query.ParentSelect ?? _selectQuery;
}
}
return(expr);
});
return(query.From.Tables[0]);
}
VirtualField MapToSource(SelectQuery.TableSource table, VirtualField field, int sourceId)
{
var visited = new HashSet <VirtualField>();
return(MapToSourceInternal(table, field, sourceId, visited));
}
