dotConnect for PostgreSQL Documentation
In This Topic
    Entity Framework Canonical Functions
    In This Topic

    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.

    * 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 for more information).
    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