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
BigCount*	v1
Count	v1
Max	v1
Min	v1
StDev	v1
StDevP	v4
Sum	v1
Var	v4
VarP	v4

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
Ceiling	v1
Floor	v1
Power	v4
Round(value)	v1
Round(value, digits)	v4
Truncate	v4

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
Contains	v4
EndsWith	v4
IndexOf	v1
Left	v1
Length	v1
LTrim	v1
Replace	v1
Reverse	v1			/*
Right	v1
RTrim	v1
Substring	v1
StartsWith	v4
ToLower	v1
ToUpper	v1
Trim	v1

* 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
AddMicroseconds	v4
AddMilliseconds	v4
AddSeconds	v4
AddMinutes	v4
AddHours	v4
AddDays	v4
AddMonths	v4
AddYears	v4
CreateDateTime	v4
CreateDateTimeOffset	v4
CreateTime	v4
CurrentDateTime	v1
CurrentDateTimeOffset	v4
CurrentUtcDateTime	v1
Day	v1
DayOfYear	v4
DiffNanoseconds	v4
DiffMilliseconds	v4
DiffMicroseconds	v4
DiffSeconds	v4
DiffMinutes	v4
DiffHours	v4
DiffDays	v4
DiffMonths	v4
DiffYears	v4
GetTotalOffsetMinutes	v1
Hour	v1
Millisecond	v1
Minute	v1
Month	v1
Second	v1
Truncate	v4
Year	v1

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
BitWiseNot	v1
BitWiseOr	v1
BitWiseXor	v1

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

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