Database Functions

Article
01/30/2023

Database functions 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.

A C# method may not have an equivalent database function.
- String.IsNullOrEmpty method translates to a null check and a comparison with an empty string in the database rather than a function.
- String.Equals(String, StringComparison) method doesn't have database equivalent since string comparison can't be represented or mimicked easily in a database.
A database function may not have an equivalent C# method. The ?? operator in C#, which doesn't have any method, translates to the COALESCE function in the database.

Types of database functions

EF Core SQL generation supports a subset of functions that can be used in databases. This limitation comes from the ability to represent a query in LINQ for the given database function. Further, each database has varying support of database functions, so EF Core provides a common subset. A database provider is free to extend EF Core SQL generation to support more patterns. Following are the types of database functions EF Core supports and uniquely identifies. These terms also help in understanding what translations come built in with EF Core providers.

Built-in vs user-defined functions

Built-in functions come with database predefined, but user-defined functions are explicitly defined by the user in the database. When EF Core translates queries to use database functions, it uses built-in functions to make sure that the function is always available on the database. The distinction of built-in functions is necessary in some databases to generate SQL correctly. For example SqlServer requires that every user-defined function is invoked with a schema-qualified name. But built-in functions in SqlServer don't have a schema. PostgreSQL defines built-in function in the public schema but they can be invoked with schema-qualified names.

Aggregate vs scalar vs table-valued functions

Scalar functions take scalar values - like integers or strings - as parameters and return a scalar value as the result. Scalar functions can be used anywhere in SQL where a scalar value can be passed.
Aggregate functions take a stream of scalar values as parameters and return a scalar value as the result. Aggregate functions are applied on the whole query result set or on a group of values generated by applying GROUP BY operator.
Table-valued functions take scalar values as parameter(s) and return a stream of rows as the result. Table-valued functions are used as a table source in FROM clause.

Niladic functions

Niladic functions are special database functions that don't have any parameters and must be invoked without parenthesis. They're similar to property/field access on an instance in C#. Niladic functions differ from parameter-less functions as the latter require empty parenthesis. There's no special name for database functions that requires parenthesis always. Another subset of database functions based on parameter count is variadic functions. Variadic functions can take varying number of parameters when invoked.

Database function mappings in EF Core

EF Core supports three 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. We usually provide 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, we also provide 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. Apart from providers, plugin writers can also add additional translations. 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. Further, since the intention of these methods is 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 custom mapping. A user-defined mapping extends the query translation according to the user needs. This functionality is useful when there are user-defined functions in the database, which the user wants to invoke from their LINQ query.

Share via