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); }