Entity Framework Canonical Functions

Article was updated on 3/4/2010

This article can be useful for programmers who want to develop 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 are 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, SQL Server 2005, and SQL
Server 2008 are marked in green.

Please note, that the “EF version” column contains the number of the Entity Framework version, in which this function is defined.

Aggregate Canonical Functions

Canonical Function Name	EF version	Oracle	MySQL	PostgreSQL	SQLite	DB2	MS SQL Server 2005	MS SQL Server 2008
Avg	v1
BigCount*	v1
Count	v1
Max	v1
Min	v1
StDev	v1
StDevP	v4
Sum	v1
Var	v4
VarP	v4

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

You can read more about Aggregate Canonical functions in MSDN.

Math Canonical Functions

Canonical Function Name	EF version	Oracle	MySQL	PostgreSQL	SQLite	DB2	MS SQL Server 2005	MS SQL Server 2008
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
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
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
TruncateTime	v4
Year	v1

* Some DBMS can store nanoseconds into the dates, but the .NET type DateTime doesn’t allow nanoseconds storage. It can recognize only 100-nanosecond intervals. In the dotConnect for SQLite implementation, AddNanoseconds does not have sufficient accuracy to store the count of individual nanoseconds and can only store an integer count of 100-nanosecond intervals. Thus, if AddNanoseconds(224) is called, only 200 nanoseconds are added to the value in the database.

You can read more about Date and Time Canonical functions in MSDN.

Bitwise Canonical Functions

Canonical Function Name	EF version	Oracle	MySQL	PostgreSQL	SQLite	DB2	MS SQL Server 2005	MS SQL Server 2008
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
NewGuid	v1			*

You can read more about this group of functions in MSDN.

* For PostgreSQL, several methods for generating GUID are supported. With dotConnect for PostgreSQL you can select the method to use with the config.QueryOptions.NewGuidGenerationMethod property (see Query Options topic of dotConnect for PostgreSQL documentation.

In conclusion, please note that the functionality of different DBMS varies. For that reason, only some of the functions can be implemented through standard routines and SQL statements. Sometimes, one and the same function can return different values, since its accuracy is different in different DBMS. This is especially true for mathematical and aggregate functions.