[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Collapse]eXpress Persistent Objects
 [Expand]Product Information
 [Expand]Getting Started
   Product Class Structure
   Database Systems Supported by XPO
  [Collapse]Data Types Supported by XPO
    Advantage Data Types Mapping
    Asa Data Types Mapping
    Ase Data Types Mapping
    DB2 Data Types Mapping
    Firebird Data Types Mapping
    Microsoft Access Data Types Mapping
    Microsoft SQL Server Data Types Mapping
    Microsoft SQL Server Compact Edition Data Types Mapping
    MySQL Data Types Mapping
    Oracle Data Types Mapping
    Pervasive Data Types Mapping
    PostgreSQL Data Types Mapping
    VistaDB Data Types Mapping
   Adding Persistence to an Existing Hierarchy
   When and Why XPO Extends the Database Schema
   Built-In Attributes
 [Expand]Feature Center
 [Expand]Design-Time Features
 [Expand]Member Tables
 [Expand]API Reference
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Data Types Supported by XPO

To store class properties in database columns, XPO provides mapping of .NET Framework types onto database-specific column types.

Expanded Simple Types

XPO supports mapping for a fixed set of simple .NET Framework types out of the box. The following topics show which database column types correspond to .NET Framework types, as implemented in built-in connection providers.

The default type mapping stores runtime values without losing precision as much as possible. However, it is allowed to map a runtime type to a different column type if the provider and the database server can convert values automatically. For example, the "smalldatetime" column type can be used for a System.DateTime type property instead of "datetime". To make XPO create a column of a specific (not default) type in the database, decorate the property with DbTypeAttribute.


Since valid value ranges provided by .NET Framework types and corresponding database column types may not match, some issues are expected. Specifically, runtime and stored values can be different due to conversion precision. Also, an exception can be thrown when a runtime value is not representable by the database column onto which it is mapped or vice versa. Therefore, avoid using extreme values like DateTime.MinValue.

Expanded Persistent Object Reference Types

Properties whose type is a reference to a persistent class are mapped to column types corresponding to the key property of the referenced class.

Expanded Enumeration Types

Properties whose type is an enumeration are mapped to column types corresponding to the Enum's underlying integral numeric type.

Expanded Structure Types

Properties whose type is a structure are mapped to multiple columns, each of which corresponds to the structure member. For more information on how to work with structures, see How to: Work with Structures.

Expanded Storing Runtime Types Not Supported By Default

Properties whose types are not listed in the above table and are not persistent references or structures are considered non-persistent.

To store a property type for which the default mapping doesn't exist, the value should be converted into one of the supported runtime types. The conversion can be implemented by introducing an additional persistent property of a supported type. For example:

To simplify and improve reusability of this pattern, XPO supports Value Converters. The value conversion logic can be put into the ValueConverter class descendant, and this converter can be associated either with a property by applying the ValueConverterAttribute attribute to the property or with a type by calling the XPDictionary.RegisterValueConverter method of the metadata dictionary.

Expanded Mapping Properties To Database Column Types Not Supported By Default

If a column type is not listed in the above table but can be converted to any of the listed type by the database, it can be read and modified by XPO. To create a column of a specific type in the database when updating the schema, decorate the property with DbTypeAttribute. If a column type cannot be converted to a supported type, it is possible to support such columns by creating a custom connection provider. Here are some examples:

Expanded Nullable Columns

When XPO updates the database schema, by default it creates columns that allow NULL values except for columns that represent the primary key. To create columns that do not allow NULL values for certain properties, set the following properties to the NullableBehavior.ByUnderlyingType value:

Also, a persistent class can be decorated with NullableBehaviorAttribute in code to enable the feature statically.

When the NullableBehavior.ByUnderlyingType option is selected, XPO creates new columns and uses the property type and property attributes to determine whether these columns have the NULL or NOT NULL flag. If the property can be set to null at runtime (a reference or nullable type), the column has the NULL flag. If the property cannot be set to null (a value type), the column has the NOT NULL flag. You can apply the NullableAttribute to override the default behavior. Note that reference type properties do not support the NullableAttribute. See Nullable Behavior for more information.

The following table summarizes flags of created columns when XPO updates the database schema depending on the NullableBehavior value.

Field type AlwaysAllowNulls ByUnderlyingType
Value type NULL NOT NULL
Nullable value type or value type with the [Nullable(true)] attribute NULL NULL
String with the [Nullable(false)] attribute NULL NOT NULL
String without the Nullable attribute or with the [Nullable(true)] attribute NULL NULL
Reference type NULL NULL

Note that columns corresponding to properties of a persistent object reference type (used as foreign keys) must allow NULL values.

The ORM Data Model Wizard is capable of retrieving the (NOT) NULL flag of existing columns and generate persistent classes accordingly. To use this functionality, set the Nullable behavior option in the wizard to "By underlying type". Refer to the Data Model Wizard and Data Model Designer documentation for details.

To specify default column values for new rows when an actual value is not included in the Insert statement, decorate properties with ColumnDefaultValueAttribute or ColumnDbDefaultValueAttribute. Use ColumnDefaultValueAttribute to set a constant default value in a database-independent manner. Use ColumnDbDefaultValueAttribute to define a default value as an arbitrary SQL expression. Note that when new objects are created by XPO, all property values are included in the Insert statement by default. So, it is necessary to initialize property values in the overridden AfterConstruction method.

Is this topic helpful?​​​​​​​