Data Types Supported by XPO

• Dec 13, 2021
• 6 minutes to read

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

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.

• 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
• SQLite Data Types Mapping
• VistaDB Data Types Mapping

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.

Note

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.

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.

Enumeration Types

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

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 Work with Structures

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:

C#

public System.Drawing.Color Color { 
    get { return System.Drawing.Color.FromArgb(ColorValue); } 
    set { ColorValue = value.ToArgb(); } 
}

[Browsable(false)] 
[Persistent("Color")] 
public int ColorValue { 
    get { return GetPropertyValue<int>(nameof(ColorValue)); } 
    set { SetPropertyValue<int>(nameof(ColorValue), value); } 
} 

VB.NET

Public Property Color() As System.Drawing.Color
    Get
        Return System.Drawing.Color.FromArgb(ColorValue)
    End Get
    Set(ByVal value As System.Drawing.Color)
        ColorValue = value.ToArgb()
    End Set
End Property

<Browsable(False), Persistent("Color")>
Public Property ColorValue() As Integer
    Get
        Return GetPropertyValue(Of Integer)(NameOf(ColorValue))
    End Get
    Set(ByVal value As Integer)
        SetPropertyValue(Of Integer)(NameOf(ColorValue), value)
    End Set
End Property

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.

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.

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:

• XpoDefault.NullableBehavior enables the feature globally
• XPDictionary.NullableBehavior enables the feature for the metadata dictionary
• XPClassInfo.NullableBehavior enables the feature for a persistent class

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.