bool IsDependent(SelectQuery.JoinedTable joinedTable, SelectQuery.JoinedTable tested)
{
var dependent = false;
var sourceId = tested.Table.Source.SourceID;
// check everyting that can be dependent on specific table
new QueryVisitor().VisitParentFirst(joinedTable, e =>
{
if (dependent)
{
return(false);
}
// do not check tested join
if (e == tested)
{
return(false);
}
if (e.ElementType == QueryElementType.SqlField)
{
var sqlField = (SqlField)e;
dependent = sqlField.Table.SourceID == sourceId;
}
return(!dependent);
});
return(dependent);
}
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);
}
bool HasDependencyWithParent(SelectQuery.JoinedTable parent,
SelectQuery.JoinedTable child)
{
var sources = new HashSet <int>(child.Table.GetTables().Select(t => t.SourceID));
var dependent = false;
new QueryVisitor().VisitParentFirst(parent, e =>
{
if (dependent)
{
return(false);
}
if (e == child)
{
return(false);
}
var expression = e as ISqlExpression;
if (expression != null)
{
var field = GetUnderlayingField(expression);
if (field != null)
{
dependent = sources.Contains(field.SourceID);
}
}
return(!dependent);
});
return(dependent);
}
bool IsDepended(SelectQuery.JoinedTable join, SelectQuery.JoinedTable toIgnore)
{
var testedSources = new HashSet <int>(join.Table.GetTables().Select(t => t.SourceID));
if (toIgnore != null)
{
foreach (var sourceId in toIgnore.Table.GetTables().Select(t => t.SourceID))
{
testedSources.Add(sourceId);
}
}
var dependent = false;
new QueryVisitor().VisitParentFirst(_selectQuery, e =>
{
if (dependent)
{
return(false);
}
// ignore non searchable parts
if (e.ElementType == QueryElementType.SelectClause ||
e.ElementType == QueryElementType.GroupByClause ||
e.ElementType == QueryElementType.OrderByClause)
{
return(false);
}
if (e.ElementType == QueryElementType.JoinedTable)
{
if (testedSources.Contains(((SelectQuery.JoinedTable)e).Table.SourceID))
{
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(null, newField, testedSources, -1);
}
}
}
return(!dependent);
});
return(dependent);
}
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);
}
protected override bool BuildJoinType(SelectQuery.JoinedTable join)
{
switch (join.JoinType)
{
case SelectQuery.JoinType.CrossApply: StringBuilder.Append("INNER JOIN LATERAL "); return(true);
case SelectQuery.JoinType.OuterApply: StringBuilder.Append("LEFT JOIN LATERAL "); return(true);
}
return(base.BuildJoinType(join));
}
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 CollectEqualFields(SelectQuery.JoinedTable join)
{
if (join.JoinType != SelectQuery.JoinType.Inner)
{
return;
}
if (join.Condition.Conditions.Any(c => c.IsOr))
{
return;
}
for (var i1 = 0; i1 < join.Condition.Conditions.Count; i1++)
{
var c = join.Condition.Conditions[i1];
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 field1 = GetUnderlayingField(predicate.Expr1);
if (field1 == null)
{
continue;
}
var field2 = GetUnderlayingField(predicate.Expr2);
if (field2 == null)
{
continue;
}
if (field1.Equals(field2))
{
continue;
}
AddEqualFields(field1, field2, join.Table.SourceID);
AddEqualFields(field2, field1, join.Table.SourceID);
}
}
public AssociatedTableContext(ExpressionBuilder builder, TableContext parent, AssociationDescriptor association)
: base(builder, parent.SelectQuery)
{
var type = association.MemberInfo.GetMemberType();
var left = association.CanBeNull;
if (typeof(IEnumerable).IsSameOrParentOf(type))
{
var etypes = type.GetGenericArguments(typeof(IEnumerable <>));
type = etypes != null && etypes.Length > 0 ? etypes[0] : type.GetListItemType();
IsList = true;
}
OriginalType = type;
ObjectType = GetObjectType();
EntityDescriptor = Builder.MappingSchema.GetEntityDescriptor(ObjectType);
SqlTable = new SqlTable(builder.MappingSchema, ObjectType);
var psrc = parent.SelectQuery.From[parent.SqlTable];
var join = left ? SqlTable.WeakLeftJoin() : IsList?SqlTable.InnerJoin() : SqlTable.WeakInnerJoin();
Association = association;
ParentAssociation = parent;
ParentAssociationJoin = join.JoinedTable;
psrc.Joins.Add(join.JoinedTable);
for (var i = 0; i < association.ThisKey.Length; i++)
{
SqlField field1;
SqlField field2;
if (!parent.SqlTable.Fields.TryGetValue(association.ThisKey[i], out field1))
{
throw new LinqException("Association key '{0}' not found for type '{1}.", association.ThisKey[i], parent.ObjectType);
}
if (!SqlTable.Fields.TryGetValue(association.OtherKey[i], out field2))
{
throw new LinqException("Association key '{0}' not found for type '{1}.", association.OtherKey[i], ObjectType);
}
join.Field(field1).Equal.Field(field2);
}
Init();
}
private bool CanRemoveSecond(SelectQuery.JoinedTable join1,
SelectQuery.JoinedTable join2)
{
var result = join1.Condition.Precedence == join2.Condition.Precedence;
if (!result)
{
return(false);
}
var table1 = join1.Table.Source as SqlTable;
var table2 = join2.Table.Source as SqlTable;
result = IsEqualTables(table1, table2);
result = result && table1 != null && table1.GetKeys(false).Count > 0; // table has keys
result = result && IsSameEqualConditions(join1, join2);
return(result);
}
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 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 IsDependedExcludeJoins(SelectQuery.JoinedTable join)
{
var testedSources = new HashSet <int>(join.Table.GetTables().Select(t => t.SourceID));
return(IsDependedExcludeJoins(testedSources));
}
private bool IsSameEqualConditions(SelectQuery.JoinedTable join1,
SelectQuery.JoinedTable join2)
{
var c1 = join1.Condition;
var c2 = join2.Condition;
Func <SelectQuery.Condition, bool> allowed =
c =>
{
var expr = c.Predicate as SelectQuery.Predicate.ExprExpr;
return(expr != null && expr.Operator == SelectQuery.Predicate.Operator.Equal && !c.IsOr);
};
if (!c1.Conditions.All(allowed) || !c2.Conditions.All(allowed))
{
return(false);
}
var result = c1.Conditions.Count == c2.Conditions.Count;
if (result)
{
Func <ISqlExpression, ISqlExpression, HashSet <int>, bool> isEqual = (e1, e2, pks) =>
{
var er = e1.ElementType == e2.ElementType && e1.Precedence == e2.Precedence;
if (er)
{
switch (e1.ElementType)
{
case QueryElementType.Column:
{
var col1 = ((SelectQuery.Column)e1).Expression as SqlField;
var col2 = ((SelectQuery.Column)e2).Expression as SqlField;
er = col1 != null && col2 != null &&
col1.Name == col2.Name &&
col1.Table.SourceID == col2.Table.SourceID;
break;
}
case QueryElementType.SqlField:
{
var col1 = (SqlField)e1;
var col2 = (SqlField)e2;
er = col1.Name == col2.Name &&
IsEqualTables(col1.Table as SqlTable, col2.Table as SqlTable);
if (er && (col1.Table.SourceID == join2.Table.SourceID || col2.Table.SourceID == join2.Table.SourceID))
// if (er && IsEqualTables(col1.Table as SqlTable, join2.Table.Source as SqlTable))
{
if (col1.IsPrimaryKey && col2.IsPrimaryKey)
{
pks.Add(Math.Max(1, col1.PrimaryKeyOrder));
}
}
break;
}
default:
er = false;
break;
}
}
return(er);
};
var c2C = c2.Conditions.ToList();
var primary = new HashSet <int>();
for (int i1 = 0; i1 < c1.Conditions.Count; i1++)
{
var it1 = c1.Conditions[i1].Predicate as SelectQuery.Predicate.ExprExpr;
if (it1 == null)
{
result = false;
break;
}
var found = false;
for (int i2 = 0; i2 < c2C.Count; i2++)
{
var it2 = c2C[i2].Predicate as SelectQuery.Predicate.ExprExpr;
if (it2 == null)
{
result = false;
break;
}
if ((isEqual(it1.Expr1, it2.Expr1, primary) && isEqual(it1.Expr2, it2.Expr2, primary)) ||
(isEqual(it2.Expr1, it1.Expr1, primary) && isEqual(it2.Expr2, it1.Expr2, primary)))
{
found = true;
c2C.RemoveAt(i2);
break;
}
}
if (!found)
{
result = false;
}
if (!result)
{
break;
}
}
if (c2C.Count > 0)
{
result = false;
}
// check that all keys used in comparison
result = result && (join2.Table.Source.GetKeys(false).Count == primary.Count);
}
return(result);
}
