EF Core Providers Database Functions


Database functions (DbFunctions) are the database equivalent of C# methods. A database function can be invoked with zero or more parameters, and it computes the result based on the parameter values.

Most databases, which use SQL for querying have support for database functions. So SQL generated by EF Core query translation also allows invoking database functions. C# methods don't have to translate strictly to database functions in EF Core.

Database Function Mappings in EF Core

EF Core supports the following different ways of mapping between C# functions and database functions.

Built-in Function Mapping

By default, EF Core providers provide mappings for various built-in functions over primitive types. For example, String.ToLower() translates to LOWER in SqlServer. This functionality allows users to write queries in LINQ seamlessly.

  • It usually provides a translation in the database that gives the same result as what the C# function provides on the client-side.
  • Sometimes, to achieve that, the actual translation could be something more complicated than a database function.
  • In some scenarios, it also provides the most appropriate translation rather than matching C# semantics.
  • The same feature is also used to provide common translations for some of the C# member accesses. For example, String.Length translates to LEN in SqlServer.
  • This extensibility is useful when plugins add support for more types as primitive types and want to translate methods over them.

EF.Functions Mapping

Since not all database functions have equivalent C# functions, EF Core providers have special C# methods to invoke certain database functions.

  • These methods are defined as extension methods over EF.Functions to be used in LINQ queries.
  • These methods are provider-specific as they're closely tied with particular database functions.
  • So a method that works for one provider will likely not work for any other provider.
  • These methods intend to invoke a database function in the translated query, trying to evaluate them on the client results in an exception.

User-defined Function Mapping

Apart from mappings provided by EF Core providers, users can also define the custom mapping.

  • A user-defined mapping extends the query translation according to the user's needs.
  • This functionality is useful when there are user-defined functions in the database, which the user wants to invoke from their LINQ query.

The following are the most commonly used DbFunctions.

Function Description
Contains A DbFunction method stub that can be used in LINQ queries to target the SQL Server CONTAINS store function.
DataLength Returns the number of bytes used to represent any expression.
DateDiffDay Counts the number of day boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate).
DateDiffHour Counts the number of hour boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate).
DateDiffMicrosecond Counts the number of microsecond boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate).
DateDiffMillisecond Counts the number of millisecond boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate).
DateDiffMinute Counts the number of minute boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate).
DateDiffMonth Counts the number of month boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate).
DateDiffNanosecond Counts the number of nanosecond boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate).
DateDiffSecond Counts the number of second boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate).
DateDiffWeek Counts the number of week boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(WEEK,startDate,endDate).
DateDiffYear Counts the number of year boundaries crossed between the startDate and endDate. Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate).
DateFromParts Initializes a new instance of the DateTime structure to the specified year, month, day. Corresponds to the SQL Server's DATEFROMPARTS(year, month, day).
DateTime2FromParts Initializes a new instance of the DateTime structure to the specified year, month, day, hour, minute, second, fractions, and precision. Corresponds to the SQL Server's DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision).
DateTimeFromParts Initializes a new instance of the DateTime structure to the specified year, month, day, hour, minute, second, and millisecond. Corresponds to the SQL Server's DATETIMEFROMPARTS(year, month, day, hour, minute, second, millisecond).
DateTimeOffsetFromParts Initializes a new instance of the DateTimeOffset structure to the specified year, month, day, hour, minute, second, fractions, hourOffset, minuteOffset and precision. Corresponds to the SQL Server's DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision).
FreeText A DbFunction method stub that can be used in LINQ queries to target the SQL Server FREETEXT store function.
IsDate Validate if the given string is a valid date. Corresponds to the SQL Server's ISDATE('date').
Like An implementation of the SQL LIKE operation. On relational databases, this is usually directly translated to SQL.
SmallDateTimeFromParts Initializes a new instance of the DateTime structure to the specified year, month, day, hour, and minute. Corresponds to the SQL Server's SMALLDATETIMEFROMPARTS (year, month, day, hour, minute).
TimeFromParts Initializes a new instance of the TimeSpan structure to the specified hour, minute, second, fractions, and precision. Corresponds to the SQL Server's TIMEFROMPARTS (hour, minute, seconds, fractions, precision).

Let's have a look at the following example.

using (var context = new BookStore())
{
    var list = context.Authors
        .Include(a => a.Books)
        .Where(a => EF.Functions.Like(a.LastName, "Al%"))
        .ToList();

    foreach (var author in list)
    {
        Console.WriteLine(author.FirstName + " " + author.LastName);

        foreach (var book in author.Books)
        {
            Console.WriteLine("\t" + book.Title);
        }
    }
}

The above code will retrieve only those authors which have the LastName starts with "Al".