Mapping Data Types and Functions
- Last Updated: April 16, 2026
- 7 minute read
- ADO.NET
- Documentation
In a Database First model, the data provider uses a store-centric type mapping scheme, in which the DB2 (store) type influences the EDM type used when the model is generated.
The following table shows DB2 types are mapped to primitive types used in a Database First model. Some DB2 data types can map to several different EDM types; the default values are shown in italics.
The columns are defined as follows:
- The DB2 Type column refers to the native type name.
- The Store (SSDL) Type column refers to data types used by the store schema definition language (SSDL) file. The storage metadata schema is a formal description of the database that persists data for an application built on the EDM.
- The PrimitiveTypeKind column refers to the common data primitives used to specify the valid content of properties of entities used in defining EDM applications.
Mapping DB2 Types to EDM Types
| DB2 Type | Store (SSDL) Type | PrimitiveTypeKind |
| BIGINT | binint | Int64 |
| BINARY | binary binary_as_guid binary_as_boolean |
Binary Guid Boolean |
| BLOB | blob | Binary |
| CHAR | char char_as_boolean |
String Boolean |
| CLOB | dbclob | String |
| DATALINK | datalink | Binary |
| DATE | date | DateTime |
| DBCLOB | dbclob | String |
| DECIMAL | decimal | Decimal |
| DOUBLE | double | Double |
| DECFLOAT | decimalfloat | Double |
| FLOAT | float | Double |
| GRAPHIC | graphic graphic_as_boolean |
String Boolean |
| INTEGER | integer | Int16 |
| NUMERIC | decimal | Decimal |
| REAL | real | Single |
| ROWID | rowid | Binary |
| SMALLINT | smallint smallint_as_boolean smallint_as_byte smallint_as_sbyte |
Int16 Boolean Byte Time |
| TIME | time | Time |
| TIMESTAMP | timestamp timestamp_as_datetimeoffset |
DateTime DateTimeOffset |
| VARBINARY | varbinary | Binary |
| VARCHAR | varchar varchar_as_boolean |
String Boolean |
| VARGRAPHIC | vargraphic vargraphic_as_boolean |
String Boolean |
| XML | xml | String |
Type Mapping for Model First
The following table shows the model-centric type mapping, where the EDM Simple Types influences the Oracle (store) type used when the database is created. The columns are defined as follows:
- The PrimitiveTypeKind column refers to the common data primitives used to specify the valid content of properties of entities used in defining EDM applications.
- The Property Values Affecting Type Mapping identifies any property values that can affect type mapping.
- The Store (SSDL) column refers to data types used by the store schema definition language (SSDL) file. The storage metadata schema is a formal description of the database that persists data for an application built on the EDM.
- The DB2 Type column refers to the native type name.
Mapping EDM Types to DB2 Types
| PrimitiveTypeKind | Property Values That Affect Type Mapping | Store (SSDL) Type | DB2 Type |
| Binary | binary | Binary | |
| Boolean | smallint_as_boolean | SmallInt | |
| Byte | smallint_as_byte | Smallint | |
| DateTime | timestamp | Timestamp | |
| Decimal | decimal | Decimal | |
| Double | double | Double | |
| Guid | binary_as_guid | Binary | |
| Single | real | Real | |
| SByte | smallint_as_byte | Smallint | |
| Int16 | smallInt | Smallint | |
| Int32 | integer | Integer | |
| Int64 | bigint | BigInt | |
| String | char | Char(n) | |
| String | varchar | Varchar(n) | |
| String | clob | Clob | |
| String | graphic | Graphic | |
| String | vargraphic | Vargraphic | |
| String | dbclob | Dbclob | |
| Date | date | Date | |
| Time | time | Time | |
| DateTimeOffset | timestamp_as_datetimeoffset | Timestamp |
Type Mapping for Code First
The following table shows the model-centric type mapping, where the CLR type influences the DB2 (store) type used when the database is created. Some CLR types can map to several different DB2 types; the default values are shown in italics. ).
The columns are defined as follows:
- The CLR Type column refers to the common language runtime type name.
- The DB2 Type column refers to the native type name.
Mapping CLR Types to DB2 Data Types in a Code First Model
| CLR Type | DB2 Data Type |
| Byte | VARBINARY BINARY |
| Boolean | CHAR VARCHAR SMALLINT GRAPHIC VARGRAPHIC BINARY |
| Byte | SMALLINT |
| DateTime | TIMESTAMP |
| Decimal | DECIMAL |
| Double | DOUBLE |
| Guid | BINARY |
| Single | REAL |
| Sbyte | SMALLINT |
| Int16 | SMALLINT |
| Int32 | INTEGER |
| Int64 | BIGINT |
| String | CHAR VARCHAR GRAPHIC VARGRAPHIC |
| TimeSpan | TIME |
| DateTimeOffset | TIMESTAMP |
Mapping EDM Canonical Functions to DB2 Functions
The ADO.NET Entity Framework translates the Entity Data Model (EDM) canonical functions to the corresponding data source functionality for the ADO.NET DB2 Entity data provider. The function invocations are expressed in a common form across data sources.
Because these canonical functions are independent of data sources, argument and return types of canonical functions are defined in terms of types in the EDM. When an Entity SQL query uses canonical functions, the appropriate function is called at the data source.
Both null-input behavior and error conditions are explicitly specified for all canonical functions. However, the ADO.NET Entity Framework does not enforce this behavior. Further details are available at:
https://msdn.microsoft.com/en-us/library/bb738626.aspx
Aggregate Canonical Functions
The following table describes the mapping of EDM aggregate canonical functions to DB2 functions.
Mapping Aggregate Canonical Functions
| Aggregate Canonical Function | DB2 functions |
| Avg(expression) | avg(expression) |
| BigCount(expression) | count_big(expression) |
| Count(expression) | count(expression) |
| Max(expression) | max(expression) |
| Min(expression) | min(expression) |
| Sum(expression) | sum(expression) |
| StDev(expression) | STDDEV(expression) for LUW STDDEV_POP(expression) for others |
| StDev | STDDEV_SAMP |
| Var (expression) | VAR_SAMP (expression) |
| VarP (expression) | VAR_POP(expression) |
Date and Time Canonical Functions
The following table describes the mapping of EDM date and time canonical functions to DB2 functions.
Mapping Date and Time Canonical Functions
| Date and Time Canonical Function | DB2 Functions |
| AddDays(expression, number) | CAST(expression, AS TIMESTAMP) + (number DAYS) |
| AddHours(expression, number) | CAST(expression, AS TIMESTAMP) + (number HOURS) |
| AddMicroseconds(expression, number) | CAST(expression, AS TIMESTAMP) + (number / 1000000 ) SECONDS |
| AddMilliseconds(expression, number) | CAST(expression, AS TIMESTAMP) + (number / 1000 ) SECONDS |
| AddMinutes(expression, number) | CAST(expression, AS TIMESTAMP) + (number MINUTES) |
| AddMonths(expression, number) | CAST(expression, AS TIMESTAMP) + (number MONTHS) |
| AddNanoseconds(expression, number) | CAST(expression, AS TIMESTAMP) + (number / 1000000000 ) SECONDS) |
| AddSeconds(expression, number) | CAST(expression, AS TIMESTAMP) + (number SECONDS) |
| AddYears(expression, number) | CAST(expression, AS TIMESTAMP) + (number YEARS) |
| CreateDateTime(year, month, day, hour, minute, second) | TO_DATE(CAST(year AS VARCHAR(4)) ||'-'|| CAST(month AS VARCHAR(2)) ||'-'|| CAST(day AS VARCHAR(2)) ||' '|| CAST(hour AS VARCHAR(2)) ||':'|| CAST(minute AS VARCHAR(2)) ||':'|| CAST(CAST(second AS INTEGER) AS VARCHAR(2)) ,'YYYY-MM-DD HH24:MI:SS') |
| CreateDateTimeOffset(year, month, day, hour, minute, second, tzoffset) | VARCHAR_FORMAT( TO_DATE( CAST(year AS VARCHAR(4)) ||'-'|| CAST(month AS VARCHAR(2)) ||'-'|| CAST(day AS VARCHAR(2)) ||' '|| CAST(hour AS VARCHAR(2)) ||':' || CAST(minute AS VARCHAR(2)) ||':'|| CAST(CAST(second AS INTEGER) AS VARCHAR(2)) ,'YYYY-MM-DD HH24:MI:SS') ,'YYYY-MM-DD HH24:MI:SS.FF6') || ' ' || CAST((CASE WHEN tzoffset >= 0 THEN '+' ELSE '-' END) AS VARCHAR(2)) || CASE WHEN TRUNC(ABS((tzoffset)/60) ,0) < 10 THEN '0' ELSE '' END || TRUNC(ABS((tzoffset)/ 60),0) || ':' || CASE WHEN ABS(MOD(tzoffset, 60)) < 10 THEN '0' ELSE '' END || ABS(MOD(tzoffset, 60)) |
| CreateTime(hour, minute, second) | TIME( TO_DATE(CAST(hour AS VARCHAR(2)) ||':' || CAST(minute AS VARCHAR(2)) ||':' || CAST(CAST(second AS INTEGER) AS VARCHAR(2)) ,'HH24:MI:SS' ) ) |
| DayOfYear(expression) | DayOfYear(expression) |
| DiffDays(startExpression, endExpression) | TIMESTAMPDIFF(16, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) |
| DiffHours(startExpression, endExpression) | TIMESTAMPDIFF(8, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) |
| DiffMicroseconds(startExpression, endExpression) | TIMESTAMPDIFF(1, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) |
| DiffMilliseconds(startExpression, endExpression) | TIMESTAMPDIFF(1, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) / 1000 |
| DiffMinutes(startExpression, endExpression) | TIMESTAMPDIFF(4, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) |
| DiffMonths(startExpression, endExpression) | TIMESTAMPDIFF(64, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) |
| DiffNanoseconds(startExpression, endExpression) | TIMESTAMPDIFF(1, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) * 1000 |
| DiffSeconds(startExpression, endExpression) | TIMESTAMPDIFF(2, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) |
| DiffYears(startExpression, endExpression) | TIMESTAMPDIFF(256, CHAR( CAST(startExpression AS TIMESTAMP) - CAST(endExpression AS TIMESTAMP) ) ) |
| GetTotalOffsetMinutes (datetimeoffset) | Not supported |
| CurrentDateTime() | CURRENT_TIMESTAMP |
| CurrentDateTimeOffset() | VARCHAR(VARCHAR(CURRENT_TIMESTAMP) || ' ' || CASE WHEN HOUR(CURRENT_TIMEZONE) < 0 THEN '-' ELSE '+' END || CASE WHEN ABS(HOUR(CURRENT_TIMEZONE)) < 10 THEN '0' ELSE '' END || VARCHAR(ABS(HOUR(CURRENT_TIMEZONE))) || ':' || CASE WHEN MINUTE(CURRENT_TIMEZONE) < 10 THEN '0' ELSE '' END || VARCHAR(MINUTE(CURRENT_TIMEZONE))) |
| CurrentUtcDateTime() | (CURRENT_TIMESTAMP - CURRENT_TIMEZONE) |
| Day( expression ) | Day(expression) |
| Millisecond( expression ) | LUW: CAST(MICROSECOND( CAST( CAST( expression AS VARCHAR(26) ) AS TIMESTAMP) ) / 1000) AS INTEGER) Others: CAST(MICROSECOND( CAST(expression AS TIMESTAMP) ) / 1000) AS INTEGER) |
| Minute ( expression ) | LUW: Minute(CAST(CAST(expression AS VARCHAR(26)) AS TIMESTAMP)) Others: Minute(CAST(expression AS TIMESTAMP)) |
| Month (expression) | Month(expression) |
| Second( expression ) | LUW: Second (CAST(CAST(expression AS VARCHAR(26)) AS TIMESTAMP)) Others: Second (CAST(expression AS TIMESTAMP)) |
| TruncateTime( expression ) | DATE(CAST(expression AS TIMESTAMP)) |
| Year( expression ) | Year(expression) |
| Hour(expression) | LUW: Hour (CAST(CAST(expression AS VARCHAR(26)) AS TIMESTAMP)) Others: Hour (CAST(expression AS TIMESTAMP)) |
Math Canonical Functions
The following table describes the mapping of EDM math canonical functions to DB2 functions.
Mapping Math Canonical Functions
| Math Canonical Function | DB2 Function |
| Abs(value) | abs(value) |
| Ceiling(value) | ceiling(value) |
| Floor(value) | floor(value) |
| Power(value, exponent) | Power(CAST(value AS DOUBLE), CAST(exponent AS DOUBLE)) |
| Round(value) | ROUND(CAST(value AS DOUBLE),0) |
| Round(value, digits) | ROUND(CAST(value AS DOUBLE), CAST(digits AS INTEGER)) |
| Truncate(value, digits) | TRUNCATE(CAST( value AS DOUBLE), CAST( digits AS INTEGER)) |
String Canonical Functions
The following table describes the mapping of EDM string canonical functions to DB2 functions.
Mapping String Canonical Functions
| String Canonical Function | DB2 Function |
| Contains(string, target) | IndexOf(string, target) > 0 |
| Concat(string1,string2) | concat(string1, string2) |
| EndsWith(string, target) | IndexOf(Reverse(string),Reverse(target)) =1 |
| StartsWith(string, target) | IndexOf(string, target) =1 |
| IndexOf(target, string2) | POSSTR(target, CAST (string AS VARCHAR(4096))) |
| Left(string, length) | LEFT(CAST(string AS VARCHAR(4096)), MIN(LENGTH(CAST(string AS VARCHAR(4096))), length )) |
| Length(string) | length(string) |
| LTrim(string) | LTRIM(CAST( string AS VARCHAR(4096))) |
| Replace(string1, string2, string3) | REPLACE(CAST(string1 AS VARCHAR(4096)),CAST(string2 AS VARCHAR(4096)),CAST(string3 AS VARCHAR(4096))) |
| Reverse(string) | Not supported. See "Handling Unsupported Canonical Functions" for more information. |
| Right(string, length) | RIGHT(CAST(string AS VARCHAR(4096)), MIN(LENGTH(CAST(string AS VARCHAR(4096))), length )) |
| RTrim(string) | RTRIM(CAST( string AS VARCHAR(4096))) |
| Substring(string, start, length) | SUBSTR(CAST(string AS VARCHAR(4096)), start, MIN(LENGTH(CAST(string AS VARCHAR(4096))) - (start- 1), length)) |
| ToLower(string) | LOWER(CAST(string AS VARCHAR(4096))) |
| ToUpper(string) | UPPER(CAST(string AS VARCHAR(4096))) |
| Trim(string) | TRIM(CAST( string AS VARCHAR(4096))) |
Bitwise Canonical Functions
The following table describes the mapping of EDM Bitwise canonical functions to DB2 functions.
Mapping Bitwise Canonical Functions
| Bitwise Canonical Function | DB2 Function |
| BitwiseAnd (value1, value2) | CAST( BITAND(expr1, expr2) AS type ) |
| BitwiseNot(value) | CAST( ((0 - expr1) - 1) AS type ) |
| BitwiseOr (value1, value2) | CAST( (( expr1 )+(expr2)-BITAND(exp1,expr2)) AS type ) |
| BitwiseXor (value1, value2) | CAST( (( expr1 )+(expr2)-2*BITAND(exp1,expr2)) AS type ) |
Other Canonical Functions
The following table describes the mapping of EDM other canonical functions to DB2 functions.
Mapping Other Canonical Functions
| Other Canonical Function | DB2 Function |
| NewGuid() | GENERATE_UNIQUE() || SUBSTR(HEX(RAND()),1,3) |
Handling Unsupported Canonical Functions
Because some canonical functions are not supported on the underlying database, the Entity Framework data provider allows the function call to "fall through" to the database. For example, DB2 does not support REVERSE(). To provide that functionality, you can create a custom function of the same name on the database that the data provider can call.