private DbExpression TransformIntersectOrExcept( DbExpression left, DbExpression right, DbExpressionKind expressionKind, IList <DbPropertyExpression> sortExpressionsOverLeft, string sortExpressionsBindingVariableName) { bool flag1 = expressionKind == DbExpressionKind.Except || expressionKind == DbExpressionKind.Skip; bool flag2 = expressionKind == DbExpressionKind.Except || expressionKind == DbExpressionKind.Intersect; DbExpressionBinding input = left.Bind(); DbExpressionBinding expressionBinding = right.Bind(); IList <DbPropertyExpression> propertyExpressionList1 = (IList <DbPropertyExpression>) new List <DbPropertyExpression>(); IList <DbPropertyExpression> propertyExpressionList2 = (IList <DbPropertyExpression>) new List <DbPropertyExpression>(); this.FlattenProperties((DbExpression)input.Variable, propertyExpressionList1); this.FlattenProperties((DbExpression)expressionBinding.Variable, propertyExpressionList2); if (expressionKind == DbExpressionKind.Skip && Sql8ExpressionRewriter.RemoveNonSortProperties(propertyExpressionList1, propertyExpressionList2, sortExpressionsOverLeft, input.VariableName, sortExpressionsBindingVariableName)) { expressionBinding = Sql8ExpressionRewriter.CapWithProject(expressionBinding, propertyExpressionList2); } DbExpression dbExpression1 = (DbExpression)null; for (int index = 0; index < propertyExpressionList1.Count; ++index) { DbExpression right1 = (DbExpression)propertyExpressionList1[index].Equal((DbExpression)propertyExpressionList2[index]).Or((DbExpression)propertyExpressionList1[index].IsNull().And((DbExpression)propertyExpressionList2[index].IsNull())); dbExpression1 = index != 0 ? (DbExpression)dbExpression1.And(right1) : right1; } DbExpression dbExpression2 = (DbExpression)expressionBinding.Any(dbExpression1); DbExpression predicate = !flag1 ? dbExpression2 : (DbExpression)dbExpression2.Not(); DbExpression dbExpression3 = (DbExpression)input.Filter(predicate); if (flag2) { dbExpression3 = (DbExpression)dbExpression3.Distinct(); } return(dbExpression3); }
/// <summary> /// This method is used for translating <see cref="DbIntersectExpression"/> and <see cref="DbExceptExpression"/>, /// and for translating the "Except" part of <see cref="DbSkipExpression"/>. /// into the follwoing expression: /// /// A INTERSECT B, A EXCEPT B /// /// (DISTINCT) /// | /// FILTER /// | /// | - Input: A /// | - Predicate:(NOT) /// | /// ANY /// | /// | - Input: B /// | - Predicate: (B.b1 = A.a1 or (B.b1 is null and A.a1 is null)) /// AND (B.b2 = A.a2 or (B.b2 is null and A.a2 is null)) /// AND ... /// AND (B.bn = A.an or (B.bn is null and A.an is null))) /// /// Here, A corresponds to right and B to left. /// (NOT) is present when transforming Except /// for the purpose of translating <see cref="DbExceptExpression"/> or <see cref="DbSkipExpression"/>. /// (DISTINCT) is present when transforming for the purpose of translating /// <see cref="DbExceptExpression"/> or <see cref="DbIntersectExpression"/>. /// /// For <see cref="DbSkipExpression"/>, the input to ANY is caped with project which projects out only /// the columns represented in the sortExpressionsOverLeft list and only these are used in the predicate. /// This is because we want to support skip over input with non-equal comarable columns and we have no way to recognize these. /// </summary> /// <param name="left"></param> /// <param name="right"></param> /// <param name="expressionKind"></param> /// <param name="sortExpressionsOverLeft">note that this list gets destroyed by this method</param> /// <param name="sortExpressionsBindingVariableName"></param> /// <returns></returns> private DbExpression TransformIntersectOrExcept(DbExpression left, DbExpression right, DbExpressionKind expressionKind, IList <DbPropertyExpression> sortExpressionsOverLeft, string sortExpressionsBindingVariableName) { bool negate = (expressionKind == DbExpressionKind.Except) || (expressionKind == DbExpressionKind.Skip); bool distinct = (expressionKind == DbExpressionKind.Except) || (expressionKind == DbExpressionKind.Intersect); DbExpressionBinding leftInputBinding = left.Bind(); DbExpressionBinding rightInputBinding = right.Bind(); IList <DbPropertyExpression> leftFlattenedProperties = new List <DbPropertyExpression>(); IList <DbPropertyExpression> rightFlattenedProperties = new List <DbPropertyExpression>(); FlattenProperties(leftInputBinding.Variable, leftFlattenedProperties); FlattenProperties(rightInputBinding.Variable, rightFlattenedProperties); //For Skip, we need to ignore any columns that are not in the original sort list. We can recognize these by comparing the left flattened properties and // the properties in the list sortExpressionsOverLeft // If any such columns exist, we need to add an additional project, to keep the rest of the columns from being projected, as if any among these // are non equal comparable, SQL Server 2000 throws. if (expressionKind == DbExpressionKind.Skip) { if (RemoveNonSortProperties(leftFlattenedProperties, rightFlattenedProperties, sortExpressionsOverLeft, leftInputBinding.VariableName, sortExpressionsBindingVariableName)) { rightInputBinding = CapWithProject(rightInputBinding, rightFlattenedProperties); } } Debug.Assert(leftFlattenedProperties.Count == rightFlattenedProperties.Count, "The left and the right input to INTERSECT or EXCEPT have a different number of properties"); Debug.Assert(leftFlattenedProperties.Count != 0, "The inputs to INTERSECT or EXCEPT have no properties"); //Build the predicate for the quantifier: // (B.b1 = A.a1 or (B.b1 is null and A.a1 is null)) // AND (B.b2 = A.a2 or (B.b2 is null and A.a2 is null)) // AND ... // AND (B.bn = A.an or (B.bn is null and A.an is null))) DbExpression existsPredicate = null; for (int i = 0; i < leftFlattenedProperties.Count; i++) { //A.ai == B.bi DbExpression equalsExpression = leftFlattenedProperties[i].Equal(rightFlattenedProperties[i]); //A.ai is null AND B.bi is null DbExpression leftIsNullExpression = leftFlattenedProperties[i].IsNull(); DbExpression rightIsNullExpression = rightFlattenedProperties[i].IsNull(); DbExpression bothNullExpression = leftIsNullExpression.And(rightIsNullExpression); DbExpression orExpression = equalsExpression.Or(bothNullExpression); if (i == 0) { existsPredicate = orExpression; } else { existsPredicate = existsPredicate.And(orExpression); } } //Build the quantifier DbExpression quantifierExpression = rightInputBinding.Any(existsPredicate); DbExpression filterPredicate; //Negate if needed if (negate) { filterPredicate = quantifierExpression.Not(); } else { filterPredicate = quantifierExpression; } //Build the filter DbExpression result = leftInputBinding.Filter(filterPredicate); //Apply distinct in needed if (distinct) { result = result.Distinct(); } return(result); }