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 tables show which .NET members are translated into which SQL functions when using the SQLite provider.

Binary Functions

The following table shows the binary functions mapping.

Date and Time Functions

The following table shows the DateTime functions mapping.

Some SQL has been simplified for illustration purposes. The actual SQL is more complex to handle a wider range of values.

Numeric Functions

The following table shows the numeric functions mapping.

SQL functions prefixed with ef are created by EF Core.

String Functions

The following table shows the string functions mapping.

Some SQL has been simplified for illustration purposes. The actual SQL is more complex to handle a wider range of values.

Miscellaneous Functions

The following table shows the string functions mapping.

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".