Devart Blog

Entity Framework Canonical Functions

Posted by on February 10th, 2010
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 Yes Yes Yes Yes Yes Yes Yes
Contains v4 Yes Yes
EndsWith v4 Yes Yes
IndexOf v1 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
Right v1 Yes
RTrim v1 Yes Yes Yes Yes Yes Yes Yes
Substring v1 Yes Yes Yes Yes Yes Yes Yes
StartsWith v4 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
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 Yes Yes Yes Yes Yes Yes
CurrentDateTimeOffset v4
CurrentUtcDateTime v1 Yes Yes Yes Yes Yes Yes Yes
Day v1 Yes
DayOfYear v4 Yes
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.

This article was updated on 3/4/2010. All of the tables in the article were updated, because the support for canonical functions in the latest version of dotConnect for SQLite had been extended to include the following four statistical functions (StDev, StDevP, Var, VarP) and forty-one scalar functions.

One Response to “Entity Framework Canonical Functions”

  1. Canonical Purpose “EntityFunctions.TruncateTime” doesn’t occur in MYSQL | CodersDiscuss.com Says:

    […] TruncateTime is backed by MySQL. […]