dotConnect for Oracle Documentation
Entity Framework Canonical Functions
support@devart.com

This article can be useful for programmers who want to develop a cross-database applications and use the canonical functions.

Entity Framework Canonical Functions are a set of functions, which are supported by all Entity Framework providers. These canonical functions will be translated to the corresponding data source functionality for the provider.

The tables below contain information about these functions supported by the Devart products. Functions supported by the following DBMS: MySQL, PostgreSQL, Oracle, SQLite, DB2, SQL Server 2005, and SQL Server 2008 are marked with green.

Please note, that the "EF version" column contains a number of Entity Framework version in which this function was defined. Additionally note that Entity Framework Canonical Functions are not supported in Entity Framework Core. This topic is applicable only to previous Entity Framework versions.

Aggregate Canonical Functions


Canonical Function Name EF version Oracle MySQL PostgreSQL SQLite DB2 MS SQL Server 2005 MS SQL Server 2008 - 2016
Avg v1 Yes Yes Yes Yes Yes Yes Yes
BigCount* v1 Yes Yes Yes Yes Yes Yes Yes
Count v1 Yes Yes Yes Yes Yes Yes Yes
Max v1 Yes Yes Yes Yes Yes Yes Yes
Min v1 Yes Yes Yes Yes Yes Yes Yes
StDev v1 Yes Yes Yes Yes Yes Yes Yes
StDevP v4 Yes Yes Yes Yes Yes Yes Yes
Sum v1 Yes Yes Yes Yes Yes Yes Yes
Var v4 Yes Yes Yes Yes Yes Yes Yes
VarP v4 Yes Yes Yes Yes Yes Yes Yes
You can read more about Aggregate Canonical functions in MSDN.

* Only SQL Server has an aggregate function COUNT_BIG(expr). This function is compiled to the ordinary COUNT(expr) in other databases.

Math Canonical Functions


Canonical Function Name EF version Oracle MySQL PostgreSQL SQLite DB2 MS SQL Server 2005 MS SQL Server 2008 - 2016
Abs v1 Yes Yes Yes Yes Yes Yes Yes
Ceiling v1 Yes Yes Yes Yes Yes Yes Yes
Floor v1 Yes Yes Yes Yes Yes Yes Yes
Power v4 Yes Yes Yes Yes Yes Yes Yes
Round(value) v1 Yes Yes Yes Yes Yes Yes Yes
Round(value, digits) v4 Yes Yes Yes Yes Yes Yes Yes
Truncate v4 Yes Yes Yes Yes Yes Yes Yes
You can read more about Math Canonical functions in MSDN.

String Canonical Functions


Canonical Function Name EF version Oracle MySQL PostgreSQL SQLite DB2 MS SQL Server 2005 MS SQL Server 2008 - 2016
Concat v1 Yes Yes Yes Yes Yes Yes Yes
Contains v4 Yes Yes Yes Yes Yes Yes Yes
EndsWith v4 Yes Yes Yes Yes Yes Yes Yes
IndexOf v1 Yes Yes Yes Yes Yes Yes Yes
Left v1 Yes Yes Yes Yes Yes Yes Yes
Length v1 Yes Yes Yes Yes Yes Yes Yes
LTrim v1 Yes Yes Yes Yes Yes Yes Yes
Replace v1 Yes Yes Yes Yes Yes Yes Yes
Reverse v1 Yes Yes Yes/No* Yes No Yes Yes
Right v1 Yes Yes Yes Yes Yes Yes Yes
RTrim v1 Yes Yes Yes Yes Yes Yes Yes
Substring v1 Yes Yes Yes Yes Yes Yes Yes
StartsWith v4 Yes Yes Yes Yes Yes Yes Yes
ToLower v1 Yes Yes Yes Yes Yes Yes Yes
ToUpper v1 Yes Yes Yes Yes Yes Yes Yes
Trim v1 Yes Yes Yes Yes Yes Yes Yes

* The Reverse function is supported with PostgreSQL 9.1 and higher.

You can read more about String Canonical functions in MSDN.

Date and Time Canonical Function


Canonical Function Name EF version Oracle MySQL PostgreSQL SQLite DB2 MS SQL Server 2005 MS SQL Server 2008 - 2016
AddNanoseconds* v4 Yes No Yes Yes No Yes Yes
AddMicroseconds v4 Yes No Yes Yes Yes Yes Yes
AddMilliseconds v4 Yes No Yes Yes Yes Yes Yes
AddSeconds v4 Yes Yes Yes Yes Yes Yes Yes
AddMinutes v4 Yes Yes Yes Yes Yes Yes Yes
AddHours v4 Yes Yes Yes Yes Yes Yes Yes
AddDays v4 Yes Yes Yes Yes Yes Yes Yes
AddMonths v4 Yes Yes Yes Yes Yes Yes Yes
AddYears v4 Yes Yes Yes Yes Yes Yes Yes
CreateDateTime v4 Yes Yes Yes Yes Yes Yes Yes
CreateDateTimeOffset v4 Yes No Yes Yes No No Yes
CreateTime v4 Yes Yes Yes Yes Yes No Yes
CurrentDateTime v1 Yes Yes Yes Yes Yes Yes Yes
CurrentDateTimeOffset v4 Yes No Yes Yes No No Yes
CurrentUtcDateTime v1 Yes Yes Yes Yes Yes Yes Yes
Day v1 Yes Yes Yes Yes Yes Yes Yes
DayOfYear v4 Yes Yes Yes Yes Yes Yes Yes
DiffNanoseconds v4 No No No No No Yes Yes
DiffMilliseconds v4 No No No No Yes Yes Yes
DiffMicroseconds v4 No No No No Yes Yes Yes
DiffSeconds v4 No No No No Yes Yes Yes
DiffMinutes v4 No No No No Yes Yes Yes
DiffHours v4 No No No No Yes Yes Yes
DiffDays v4 Yes Yes Yes Yes Yes Yes Yes
DiffMonths v4 Yes Yes Yes Yes Yes Yes Yes
DiffYears v4 Yes Yes Yes Yes Yes Yes Yes
GetTotalOffsetMinutes v1 Yes No No Yes No No Yes
Hour v1 Yes Yes Yes Yes Yes Yes Yes
Millisecond v1 Yes Yes Yes Yes Yes Yes Yes
Minute v1 Yes Yes Yes Yes Yes Yes Yes
Month v1 Yes Yes Yes Yes Yes Yes Yes
Second v1 Yes Yes Yes Yes Yes Yes Yes
Truncate v4 Yes Yes Yes Yes Yes Yes Yes
Year v1 Yes Yes Yes Yes Yes Yes Yes
You can read more about Date and Time Canonical functions in MSDN.

* Some DBMS can store nanoseconds into the dates, but .NET type DateTime doesn't allow nanoseconds storage. It can recognize only 100-nanoseconds intervals. The AddNanoseconds in dotConnect for SQLite realization can add only integer value of the 100-nanoseconds intervals. So, if you call AddNanoseconds(224), in the database to the value will be added only 200 nanoseconds.

Bitwise Canonical Functions


Canonical Function Name EF version Oracle MySQL PostgreSQL SQLite DB2 MS SQL Server 2005 MS SQL Server 2008 - 2016
BitWiseAnd v1 Yes Yes Yes Yes Yes Yes Yes
BitWiseNot v1 No Yes Yes Yes Yes Yes Yes
BitWiseOr v1 Yes Yes Yes Yes Yes Yes Yes
BitWiseXor v1 Yes Yes Yes Yes Yes Yes Yes
You can read more about Bitwise Canonical functions in MSDN.

Other Canonical Functions


Canonical Function Name EF version Oracle MySQL PostgreSQL SQLite DB2 MS SQL Server 2005 MS SQL Server 2008 - 2016
NewGuid v1 Yes No Yes Yes Yes Yes Yes
You can read more about this group of functions in MSDN.


In conclusion, please note that the functionality of the different DBMS varies. It is reason why not all the functions can be implemented by standard routines and SQL statements. Sometimes similar functions can return different values because of different accuracy. Especially it concerns mathematic and aggregate functions.

See Also

Entity Framework Support |  Entity Framework Tutorial