Code:
/ 4.0 / 4.0 / DEVDIV_TFS / Dev10 / Releases / RTMRel / ndp / fx / src / DLinq / Dlinq / SqlClient / Query / SqlCaseSimplifier.cs / 1305376 / SqlCaseSimplifier.cs
using System; using System.Collections.Generic; using System.Data.Linq; using System.Data.Linq.Provider; using System.Diagnostics.CodeAnalysis; namespace System.Data.Linq.SqlClient { ////// SQL with CASE statements is harder to read. This visitor attempts to reduce CASE /// statements to equivalent (but easier to read) logic. /// internal class SqlCaseSimplifier { internal static SqlNode Simplify(SqlNode node, SqlFactory sql) { return new Visitor(sql).Visit(node); } class Visitor : SqlVisitor { SqlFactory sql; internal Visitor(SqlFactory sql) { this.sql = sql; } ////// Replace equals and not equals: /// /// | CASE XXX | CASE XXX CASE XXX /// | WHEN AAA THEN MMMM | != RRRR ===> WHEN AAA THEN (MMMM != RRRR) ==> WHEN AAA THEN true /// | WHEN BBB THEN NNNN | WHEN BBB THEN (NNNN != RRRR) WHEN BBB THEN false /// | etc. | etc. etc. /// | ELSE OOOO | ELSE (OOOO != RRRR) ELSE true /// | END END END /// /// Where MMMM, NNNN and RRRR are constants. /// internal override SqlExpression VisitBinaryOperator(SqlBinary bo) { switch (bo.NodeType) { case SqlNodeType.EQ: case SqlNodeType.NE: case SqlNodeType.EQ2V: case SqlNodeType.NE2V: if (bo.Left.NodeType == SqlNodeType.SimpleCase && bo.Right.NodeType == SqlNodeType.Value && AreCaseWhenValuesConstant((SqlSimpleCase)bo.Left)) { return this.DistributeOperatorIntoCase(bo.NodeType, (SqlSimpleCase)bo.Left, bo.Right); } else if (bo.Right.NodeType == SqlNodeType.SimpleCase && bo.Left.NodeType==SqlNodeType.Value && AreCaseWhenValuesConstant((SqlSimpleCase)bo.Right)) { return this.DistributeOperatorIntoCase(bo.NodeType, (SqlSimpleCase)bo.Right, bo.Left); } break; } return base.VisitBinaryOperator(bo); } ////// Checks to see if all SqlSimpleCase when values are of Value type. /// [SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic", Justification="Unknown reason.")] internal bool AreCaseWhenValuesConstant(SqlSimpleCase sc) { foreach (SqlWhen when in sc.Whens) { if (when.Value.NodeType != SqlNodeType.Value) { return false; } } return true; } ////// Helper for VisitBinaryOperator. Builds the new case with distributed valueds. /// private SqlExpression DistributeOperatorIntoCase(SqlNodeType nt, SqlSimpleCase sc, SqlExpression expr) { if (nt!=SqlNodeType.EQ && nt!=SqlNodeType.NE && nt!=SqlNodeType.EQ2V && nt!=SqlNodeType.NE2V) throw Error.ArgumentOutOfRange("nt"); object val = Eval(expr); Listvalues = new List (); List matches = new List (); foreach(SqlWhen when in sc.Whens) { matches.Add(when.Match); object whenVal = Eval(when.Value); bool eq = when.Value.SqlType.AreValuesEqual(whenVal, val); values.Add(sql.ValueFromObject((nt==SqlNodeType.EQ || nt==SqlNodeType.EQ2V) == eq, false, sc.SourceExpression)); } return this.VisitExpression(sql.Case(typeof(bool), sc.Expression, matches, values, sc.SourceExpression)); } internal override SqlExpression VisitSimpleCase(SqlSimpleCase c) { c.Expression = this.VisitExpression(c.Expression); int compareWhen = 0; // Find the ELSE if it exists. for (int i = 0, n = c.Whens.Count; i < n; i++) { if (c.Whens[i].Match == null) { compareWhen = i; break; } } c.Whens[compareWhen].Match = VisitExpression(c.Whens[compareWhen].Match); c.Whens[compareWhen].Value = VisitExpression(c.Whens[compareWhen].Value); // Compare each other when value to the compare when List newWhens = new List (); bool allValuesLiteral = true; for (int i = 0, n = c.Whens.Count; i < n; i++) { if (compareWhen != i) { SqlWhen when = c.Whens[i]; when.Match = this.VisitExpression(when.Match); when.Value = this.VisitExpression(when.Value); if (!SqlComparer.AreEqual(c.Whens[compareWhen].Value, when.Value)) { newWhens.Add(when); } allValuesLiteral = allValuesLiteral && when.Value.NodeType == SqlNodeType.Value; } } newWhens.Add(c.Whens[compareWhen]); // Did everything reduce to a single CASE? SqlExpression rewrite = TryToConsolidateAllValueExpressions(newWhens.Count, c.Whens[compareWhen].Value); if (rewrite != null) return rewrite; // Can it be a conjuction (or disjunction) of clauses? rewrite = TryToWriteAsSimpleBooleanExpression(c.ClrType, c.Expression, newWhens, allValuesLiteral); if (rewrite != null) return rewrite; // Can any WHEN clauses be reduced to fall into the ELSE clause? rewrite = TryToWriteAsReducedCase(c.ClrType, c.Expression, newWhens, c.Whens[compareWhen].Match, c.Whens.Count); if (rewrite != null) return rewrite; return c; } /// /// When there is exactly one when clause in the CASE: /// /// CASE XXX /// WHEN AAA THEN YYY ===> YYY /// END /// /// Then, just reduce it to the value. /// [SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic", Justification="Unknown reason.")] private SqlExpression TryToConsolidateAllValueExpressions(int valueCount, SqlExpression value) { if (valueCount == 1) { return value; } return null; } ////// For CASE statements which represent boolean values: /// /// CASE XXX /// WHEN AAA THEN true ===> (XXX==AAA) || (XXX==BBB) /// WHEN BBB THEN true /// ELSE false /// etc. /// END /// /// Also, /// /// CASE XXX /// WHEN AAA THEN false ===> (XXX!=AAA) && (XXX!=BBB) /// WHEN BBB THEN false /// ELSE true /// etc. /// END /// /// The reduce to a conjunction or disjunction of equality or inequality. /// The possibility of NULL in XXX is taken into account. /// private SqlExpression TryToWriteAsSimpleBooleanExpression(Type caseType, SqlExpression discriminator, ListnewWhens, bool allValuesLiteral) { SqlExpression rewrite = null; if (caseType == typeof(bool) && allValuesLiteral) { bool? holdsNull = SqlExpressionNullability.CanBeNull(discriminator); // The discriminator can't hold a NULL. // In this case, we don't need the special fallback that CASE-ELSE gives. // We can just construct a boolean operation. bool? whenValue = null; for (int i = 0; i < newWhens.Count; ++i) { SqlValue lit = (SqlValue)newWhens[i].Value; // Must be SqlValue because of allValuesLiteral. bool value = (bool)lit.Value; // Must be bool because of caseType==typeof(bool). if (newWhens[i].Match != null) { // Skip the ELSE if (value) { rewrite = sql.OrAccumulate(rewrite, sql.Binary(SqlNodeType.EQ, discriminator, newWhens[i].Match)); } else { rewrite = sql.AndAccumulate(rewrite, sql.Binary(SqlNodeType.NE, discriminator, newWhens[i].Match)); } } else { whenValue = value; } } // If it could possibly hold null values. if (holdsNull != false && whenValue != null) { if (whenValue == true) { rewrite = sql.OrAccumulate(rewrite, sql.Unary(SqlNodeType.IsNull, discriminator, discriminator.SourceExpression)); } else { rewrite = sql.AndAccumulate(rewrite, sql.Unary(SqlNodeType.IsNotNull, discriminator, discriminator.SourceExpression)); } } } return rewrite; } /// /// Remove any WHEN clauses which have the same value as ELSE. /// /// CASE XXX CASE XXX /// WHEN AAA THEN YYY ===> WHEN AAA THEN YYY /// WHEN BBB THEN ZZZ WHEN CCC THEN YYY /// WHEN CCC THEN YYY ELSE ZZZ /// ELSE ZZZ END /// END /// /// [SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic", Justification="Unknown reason.")] private SqlExpression TryToWriteAsReducedCase(Type caseType, SqlExpression discriminator, ListnewWhens, SqlExpression elseCandidate, int originalWhenCount) { if (newWhens.Count != originalWhenCount) { // Some whens were the same as the comparand. if (elseCandidate == null) { // -and- the comparand is ELSE (value == null). // In this case, simplify the CASE to eliminate everything equivalent to ELSE. return new SqlSimpleCase(caseType, discriminator, newWhens, discriminator.SourceExpression); } } return null; } } } } // File provided for Reference Use Only by Microsoft Corporation (c) 2007. // Copyright (c) Microsoft Corporation. All rights reserved. using System; using System.Collections.Generic; using System.Data.Linq; using System.Data.Linq.Provider; using System.Diagnostics.CodeAnalysis; namespace System.Data.Linq.SqlClient { /// /// SQL with CASE statements is harder to read. This visitor attempts to reduce CASE /// statements to equivalent (but easier to read) logic. /// internal class SqlCaseSimplifier { internal static SqlNode Simplify(SqlNode node, SqlFactory sql) { return new Visitor(sql).Visit(node); } class Visitor : SqlVisitor { SqlFactory sql; internal Visitor(SqlFactory sql) { this.sql = sql; } ////// Replace equals and not equals: /// /// | CASE XXX | CASE XXX CASE XXX /// | WHEN AAA THEN MMMM | != RRRR ===> WHEN AAA THEN (MMMM != RRRR) ==> WHEN AAA THEN true /// | WHEN BBB THEN NNNN | WHEN BBB THEN (NNNN != RRRR) WHEN BBB THEN false /// | etc. | etc. etc. /// | ELSE OOOO | ELSE (OOOO != RRRR) ELSE true /// | END END END /// /// Where MMMM, NNNN and RRRR are constants. /// internal override SqlExpression VisitBinaryOperator(SqlBinary bo) { switch (bo.NodeType) { case SqlNodeType.EQ: case SqlNodeType.NE: case SqlNodeType.EQ2V: case SqlNodeType.NE2V: if (bo.Left.NodeType == SqlNodeType.SimpleCase && bo.Right.NodeType == SqlNodeType.Value && AreCaseWhenValuesConstant((SqlSimpleCase)bo.Left)) { return this.DistributeOperatorIntoCase(bo.NodeType, (SqlSimpleCase)bo.Left, bo.Right); } else if (bo.Right.NodeType == SqlNodeType.SimpleCase && bo.Left.NodeType==SqlNodeType.Value && AreCaseWhenValuesConstant((SqlSimpleCase)bo.Right)) { return this.DistributeOperatorIntoCase(bo.NodeType, (SqlSimpleCase)bo.Right, bo.Left); } break; } return base.VisitBinaryOperator(bo); } ////// Checks to see if all SqlSimpleCase when values are of Value type. /// [SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic", Justification="Unknown reason.")] internal bool AreCaseWhenValuesConstant(SqlSimpleCase sc) { foreach (SqlWhen when in sc.Whens) { if (when.Value.NodeType != SqlNodeType.Value) { return false; } } return true; } ////// Helper for VisitBinaryOperator. Builds the new case with distributed valueds. /// private SqlExpression DistributeOperatorIntoCase(SqlNodeType nt, SqlSimpleCase sc, SqlExpression expr) { if (nt!=SqlNodeType.EQ && nt!=SqlNodeType.NE && nt!=SqlNodeType.EQ2V && nt!=SqlNodeType.NE2V) throw Error.ArgumentOutOfRange("nt"); object val = Eval(expr); Listvalues = new List (); List matches = new List (); foreach(SqlWhen when in sc.Whens) { matches.Add(when.Match); object whenVal = Eval(when.Value); bool eq = when.Value.SqlType.AreValuesEqual(whenVal, val); values.Add(sql.ValueFromObject((nt==SqlNodeType.EQ || nt==SqlNodeType.EQ2V) == eq, false, sc.SourceExpression)); } return this.VisitExpression(sql.Case(typeof(bool), sc.Expression, matches, values, sc.SourceExpression)); } internal override SqlExpression VisitSimpleCase(SqlSimpleCase c) { c.Expression = this.VisitExpression(c.Expression); int compareWhen = 0; // Find the ELSE if it exists. for (int i = 0, n = c.Whens.Count; i < n; i++) { if (c.Whens[i].Match == null) { compareWhen = i; break; } } c.Whens[compareWhen].Match = VisitExpression(c.Whens[compareWhen].Match); c.Whens[compareWhen].Value = VisitExpression(c.Whens[compareWhen].Value); // Compare each other when value to the compare when List newWhens = new List (); bool allValuesLiteral = true; for (int i = 0, n = c.Whens.Count; i < n; i++) { if (compareWhen != i) { SqlWhen when = c.Whens[i]; when.Match = this.VisitExpression(when.Match); when.Value = this.VisitExpression(when.Value); if (!SqlComparer.AreEqual(c.Whens[compareWhen].Value, when.Value)) { newWhens.Add(when); } allValuesLiteral = allValuesLiteral && when.Value.NodeType == SqlNodeType.Value; } } newWhens.Add(c.Whens[compareWhen]); // Did everything reduce to a single CASE? SqlExpression rewrite = TryToConsolidateAllValueExpressions(newWhens.Count, c.Whens[compareWhen].Value); if (rewrite != null) return rewrite; // Can it be a conjuction (or disjunction) of clauses? rewrite = TryToWriteAsSimpleBooleanExpression(c.ClrType, c.Expression, newWhens, allValuesLiteral); if (rewrite != null) return rewrite; // Can any WHEN clauses be reduced to fall into the ELSE clause? rewrite = TryToWriteAsReducedCase(c.ClrType, c.Expression, newWhens, c.Whens[compareWhen].Match, c.Whens.Count); if (rewrite != null) return rewrite; return c; } /// /// When there is exactly one when clause in the CASE: /// /// CASE XXX /// WHEN AAA THEN YYY ===> YYY /// END /// /// Then, just reduce it to the value. /// [SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic", Justification="Unknown reason.")] private SqlExpression TryToConsolidateAllValueExpressions(int valueCount, SqlExpression value) { if (valueCount == 1) { return value; } return null; } ////// For CASE statements which represent boolean values: /// /// CASE XXX /// WHEN AAA THEN true ===> (XXX==AAA) || (XXX==BBB) /// WHEN BBB THEN true /// ELSE false /// etc. /// END /// /// Also, /// /// CASE XXX /// WHEN AAA THEN false ===> (XXX!=AAA) && (XXX!=BBB) /// WHEN BBB THEN false /// ELSE true /// etc. /// END /// /// The reduce to a conjunction or disjunction of equality or inequality. /// The possibility of NULL in XXX is taken into account. /// private SqlExpression TryToWriteAsSimpleBooleanExpression(Type caseType, SqlExpression discriminator, ListnewWhens, bool allValuesLiteral) { SqlExpression rewrite = null; if (caseType == typeof(bool) && allValuesLiteral) { bool? holdsNull = SqlExpressionNullability.CanBeNull(discriminator); // The discriminator can't hold a NULL. // In this case, we don't need the special fallback that CASE-ELSE gives. // We can just construct a boolean operation. bool? whenValue = null; for (int i = 0; i < newWhens.Count; ++i) { SqlValue lit = (SqlValue)newWhens[i].Value; // Must be SqlValue because of allValuesLiteral. bool value = (bool)lit.Value; // Must be bool because of caseType==typeof(bool). if (newWhens[i].Match != null) { // Skip the ELSE if (value) { rewrite = sql.OrAccumulate(rewrite, sql.Binary(SqlNodeType.EQ, discriminator, newWhens[i].Match)); } else { rewrite = sql.AndAccumulate(rewrite, sql.Binary(SqlNodeType.NE, discriminator, newWhens[i].Match)); } } else { whenValue = value; } } // If it could possibly hold null values. if (holdsNull != false && whenValue != null) { if (whenValue == true) { rewrite = sql.OrAccumulate(rewrite, sql.Unary(SqlNodeType.IsNull, discriminator, discriminator.SourceExpression)); } else { rewrite = sql.AndAccumulate(rewrite, sql.Unary(SqlNodeType.IsNotNull, discriminator, discriminator.SourceExpression)); } } } return rewrite; } /// /// Remove any WHEN clauses which have the same value as ELSE. /// /// CASE XXX CASE XXX /// WHEN AAA THEN YYY ===> WHEN AAA THEN YYY /// WHEN BBB THEN ZZZ WHEN CCC THEN YYY /// WHEN CCC THEN YYY ELSE ZZZ /// ELSE ZZZ END /// END /// /// [SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic", Justification="Unknown reason.")] private SqlExpression TryToWriteAsReducedCase(Type caseType, SqlExpression discriminator, ListnewWhens, SqlExpression elseCandidate, int originalWhenCount) { if (newWhens.Count != originalWhenCount) { // Some whens were the same as the comparand. if (elseCandidate == null) { // -and- the comparand is ELSE (value == null). // In this case, simplify the CASE to eliminate everything equivalent to ELSE. return new SqlSimpleCase(caseType, discriminator, newWhens, discriminator.SourceExpression); } } return null; } } } } // File provided for Reference Use Only by Microsoft Corporation (c) 2007. // Copyright (c) Microsoft Corporation. All rights reserved.
Link Menu
This book is available now!
Buy at Amazon US or
Buy at Amazon UK
- MdiWindowListStrip.cs
- Ref.cs
- ExtendedPropertyCollection.cs
- SiteMapNodeCollection.cs
- UnsignedPublishLicense.cs
- MatrixAnimationBase.cs
- TrustManager.cs
- EncoderParameters.cs
- Membership.cs
- TypeLibConverter.cs
- QueryStringParameter.cs
- XmlSerializerFactory.cs
- WorkflowServiceAttributes.cs
- OracleColumn.cs
- RangeContentEnumerator.cs
- KeyBinding.cs
- SelectedDatesCollection.cs
- DocumentPageView.cs
- BinaryUtilClasses.cs
- OdbcDataReader.cs
- _ServiceNameStore.cs
- PictureBox.cs
- CollectionViewGroup.cs
- EventLogTraceListener.cs
- SQLInt64.cs
- TableLayoutRowStyleCollection.cs
- ObjectDataProvider.cs
- RuntimeResourceSet.cs
- Rotation3DAnimationUsingKeyFrames.cs
- FreeFormPanel.cs
- AsymmetricSignatureFormatter.cs
- XslCompiledTransform.cs
- MimeTypePropertyAttribute.cs
- ComponentResourceKeyConverter.cs
- DateTimeUtil.cs
- PasswordPropertyTextAttribute.cs
- UserPreferenceChangedEventArgs.cs
- DataProtection.cs
- ReturnValue.cs
- PagedDataSource.cs
- IndependentAnimationStorage.cs
- SignatureGenerator.cs
- TableDetailsCollection.cs
- NavigationEventArgs.cs
- UriPrefixTable.cs
- ExpressionNode.cs
- ExpandCollapsePattern.cs
- Buffer.cs
- RootBuilder.cs
- RelationshipEntry.cs
- TextStore.cs
- ApplicationManager.cs
- IOThreadTimer.cs
- CompilationUtil.cs
- ProviderUtil.cs
- TouchDevice.cs
- AxDesigner.cs
- EventNotify.cs
- ListBox.cs
- TreeNodeStyleCollection.cs
- PackWebRequest.cs
- CodeMemberMethod.cs
- TimeoutValidationAttribute.cs
- PackWebRequest.cs
- XsdSchemaFileEditor.cs
- InheritanceRules.cs
- Convert.cs
- ClientUtils.cs
- WindowsFont.cs
- UnsafeNativeMethods.cs
- DoubleConverter.cs
- DataGridViewCheckBoxCell.cs
- TrustLevel.cs
- OperatingSystem.cs
- Array.cs
- DurableInstanceProvider.cs
- ObjectList.cs
- TableDetailsCollection.cs
- EventHandlerList.cs
- SspiHelper.cs
- TemporaryBitmapFile.cs
- QilList.cs
- NumberFormatter.cs
- ThreadExceptionEvent.cs
- Stream.cs
- NativeMethods.cs
- ButtonBaseAdapter.cs
- DrawingAttributes.cs
- Expander.cs
- OutputCacheProfile.cs
- KeyGesture.cs
- TextDecorationCollection.cs
- ListDictionary.cs
- HybridWebProxyFinder.cs
- ClientUtils.cs
- MimeTypeAttribute.cs
- smtpconnection.cs
- AssemblyBuilder.cs
- Region.cs
- ProjectionCamera.cs