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

.NET SQL
bytes.Contains(value) instr(@bytes, char(@value)) > 0
bytes.Length length(@bytes)
bytes.SequenceEqual(second) @bytes = @second

Date and Time Functions

The following table shows the DateTime functions mapping.

.NET SQL
DateTime.Now datetime('now', 'localtime')
DateTime.Today datetime('now', 'localtime', 'start of day')
DateTime.UtcNow datetime('now')
dateTime.AddDays(value) datetime(@dateTime, @value || ' days')
dateTime.AddHours(value) datetime(@dateTime, @d || ' hours')
dateTime.AddMilliseconds(value) datetime(@dateTime, (@value / 1000.0) || ' seconds')
dateTime.AddMinutes(value) datetime(@dateTime, @value || ' minutes')
dateTime.AddMonths(months) datetime(@dateTime, @months || ' months')
dateTime.AddSeconds(value) datetime(@dateTime, @value || ' seconds')
dateTime.AddTicks(value) datetime(@dateTime, (@value / 10000000.0) || ' seconds')
dateTime.AddYears(value) datetime(@dateTime, @value || ' years')
dateTime.Date datetime(@dateTime, 'start of day')
dateTime.Day strftime('%d', @dateTime)
dateTime.DayOfWeek strftime('%w', @dateTime)
dateTime.DayOfYear strftime('%j', @dateTime)
dateTime.Hour strftime('%H', @dateTime)
dateTime.Millisecond (strftime('%f', @dateTime) * 1000) % 1000
dateTime.Minute strftime('%M', @dateTime)
dateTime.Month strftime('%m', @dateTime)
dateTime.Second strftime('%S', @dateTime)
dateTime.Ticks (julianday(@dateTime) - julianday('0001-01-01 00:00:00')) * 864000000000
dateTime.TimeOfDay time(@dateTime)
dateTime.Year strftime('%Y', @dateTime)

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.

.NET SQL
-decimalValue ef_negate(@decimalValue)
decimalValue - d ef_add(@decimalValue, ef_negate(@d))
decimalValue * d ef_multiply(@decimalValue, @d)
decimalValue / d ef_divide(@decimalValue, @d)
decimalValue % d ef_mod(@decimalValue, @d)
decimalValue + d ef_add(@decimalValue, @d)
decimalValue < d ef_compare(@decimalValue, @d) < 0
decimalValue <= d ef_compare(@decimalValue, @d) <= 0
decimalValue > d ef_compare(@decimalValue, @d) > 0
decimalValue >= d ef_compare(@decimalValue, @d) >= 0
doubleValue % d ef_mod(@doubleValue, @d)
floatValue % d ef_mod(@floatValue, @d)
Math.Abs(value) abs(@value)
Math.Max(val1, val2) max(@val1, @val2)
Math.Min(val1, val2) min(@val1, @val2)
Math.Round(d) round(@d)
Math.Round(d, digits) round(@d, @digits)

SQL functions prefixed with ef are created by EF Core.

String Functions

The following table shows the string functions mapping.

.NET SQL
EF.Functions.Collate(operand, collation) @operand COLLATE @collation
EF.Functions.Like(matchExpression, pattern) @matchExpression LIKE @pattern
EF.Functions.Like(matchExpression, pattern, escapeCharacter) @matchExpression LIKE @pattern ESCAPE @escapeCharacter
string.Compare(strA, strB) CASE WHEN @strA = @strB THEN 0 ... END
string.Concat(str0, str1) @str0 || @str1
string.IsNullOrEmpty(value) @value IS NULL OR @value = ''
string.IsNullOrWhiteSpace(value) @value IS NULL OR trim(@value) = ''
stringValue.CompareTo(strB) CASE WHEN @stringValue = @strB THEN 0 ... END
stringValue.Contains(value) instr(@stringValue, @value) > 0
stringValue.EndsWith(value) @stringValue LIKE '%' || @value
stringValue.FirstOrDefault() substr(@stringValue, 1, 1)
stringValue.IndexOf(value) instr(@stringValue, @value) - 1
stringValue.LastOrDefault() substr(@stringValue, length(@stringValue), 1)
stringValue.Length length(@stringValue)
stringValue.Replace(oldValue, newValue) replace(@stringValue, @oldValue, @newValue)
stringValue.StartsWith(value) @stringValue LIKE @value || '%'
stringValue.Substring(startIndex, length) substr(@stringValue, @startIndex + 1, @length)
stringValue.ToLower() lower(@stringValue)
stringValue.ToUpper() upper(@stringValue)
stringValue.Trim() trim(@stringValue)
stringValue.Trim(trimChar) trim(@stringValue, @trimChar)
stringValue.TrimEnd() rtrim(@stringValue)
stringValue.TrimEnd(trimChar) rtrim(@stringValue, @trimChar)
stringValue.TrimStart() ltrim(@stringValue)
stringValue.TrimStart(trimChar) ltrim(@stringValue, @trimChar)

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.

.NET SQL
collection.Contains(item) @item IN @collection
enumValue.HasFlag(flag) @enumValue & @flag = @flag
nullable.GetValueOrDefault() coalesce(@nullable, 0)
nullable.GetValueOrDefault(defaultValue) coalesce(@nullable, @defaultValue)

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