Breaking Change T889138
Visible to All Users

XPO may throw InvalidCastException or SqlExecutionErrorException if parameter types (for update/read queries) do not match database column types

What Changed

XPO specifies type and size information for DbParameter passed to DbCommand. To do so, XPO first determines persistent field/property type, then looks up database type mappings.

Consider the following scenarios:

  • UPDATE operations (XPO saves persistent objects): XPO looks up mappings listed in Data Types Supported by XPO and sets DbCommand parameter types.
  • READ operations (XPO queries persistent objects): PersistentAliasAttribute, XPCollection, Session.FindObject, and other API may use filter expressions (CriteriaOperator) with parameters. XPO determines the types of included parameters based on the types of corresponding persistent fields/properties. For instance, if the filter condition is Age > ?, then the parameter will have the same type as persistent field Age.

Reasons for Change

  • Support for Microsoft SQL Server's Always Encrypted functionality that requires type and size information for DbParameter.
  • Better support for Command.Prepare in certain database providers.
  • Detection of problematic customer code.
  • Performance and other internal optimizations in certain database providers.

Impact on Existing Apps

XPO apps may throw InvalidCastException and SqlExecutionErrorException in the following scenarios:

  • UPDATE operations: Database column type doesn't match persistent field/property type (no supported mapping exists). For instance, XPO raises an exception if you use SQL Server and map a System.Int32 property to a varchar column. Expected Microsoft SQL Server mapping for System.Int32 is int.
  • READ operations: CriteriaOperator parameter types are incompatible with persistent field/property types. For instance, XPO raises an exception if an expression compares a System.Guid or System.Int32 persistent property to a string.
Code
// INCORRECT: CriteriaOperator.Parse("GuidProperty = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX') // OR CriteriaOperator.Parse("GuidProperty = ?, "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX") CriteriaOperator.Parse("IntegerProperty > '18'") // OR CriteriaOperator.Parse("IntegerProperty > ?", "18")

How to Update Existing Apps

Refer to the following instructions:

  • UPDATE operations: if you changed database column types after these columns had been created by XPO, update mappings in your data model. You can change field/property types or apply DbTypeAttribute.
  • READ operations: rewrite your custom code to match the type of CriteriaOperator parameters to corresponding persistent fields/properties. If you specify constants in criteria strings, take special note of correct type notation. For example, GUID values require curly braces.
Code
// CORRECT: CriteriaOperator.Parse("GuidProperty = {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}") // OR CriteriaOperator.Parse("GuidProperty = ?", Guid.Parse("XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX") CriteriaOperator.Parse("IntegerProperty > 18") // OR CriteriaOperator.Parse("IntegerProperty > ?", 18)

How to Revert to Previous Behavior

To turn off typed parameters, set the ConnectionProviderSql.QueryParameterMode field or ConnectionProviderSql.GlobalQueryParameterMode static field to Legacy. Take note of the following limitations in this case.

  • The Always Encrypted feature will be unavailable.
  • Performance optimization for null value parameters will be disabled (XPO will use v19.2 implementation instead of newer code).
  • Future performance optimizations may not be supported.

Changes in v20.1.4

We softened default parameter validation with regard to SELECT, UPDATE, and INSERT statements in our v20.1.4 release. For more information, see XPO - Too strict default query parameter validation in v20.1.3.

See Also

XPO may throw OverflowException if String or Byte Array value length (size) is greater than the maximum allowed database column size

Search Keywords

Failed to convert parameter value from string to guid

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.