Translations

Entity Framework Core allows providers to translate query expressions to SQL for database evaluation. For example, PostgreSQL supports regular expression operations , and the Npgsql EF Core provider automatically translates .NET's Regex.IsMatch to use this feature. Since evaluation happens at the server, table data doesn't need to be transferred to the client (saving bandwidth), and in some cases indexes can be used to speed things up. The same C# code on other providers will trigger client evaluation.

The Npgsql-specific translations are listed below. Some areas, such as full-text search , have their own pages in this section which list additional translations.

String functions

Notes string.Join(", ", agg_strings) string_agg(agg_strings, ', ') Added in 7.0, see Aggregate functions . EF.Functions.StringToArray(s, "|") string_agg(s, '|') Added in 8.0 EF.Functions.StringToArray(s, "|", "FOO") string_agg(s, '|', 'FOO') Added in 8.0

Date and time functions

Some of the operations below depend on the concept of a "local time zone" (e.g. DateTime.Today ). While in .NET this is the machine time zone where .NET is running, the corresponding PostgreSQL translations use the TimeZone connection parameter as the local time zone.

Since version 6.0, many of the below DateTime translations are also supported on DateTimeOffset.

See also Npgsql's NodaTime support , which is a better and safer way of interacting with date/time data.

dateTimeOffset.UtcDateTime No PG operation (.NET-side conversion from DateTimeOffset to DateTime only) Added in 6.0 dateTimeOffset.LocalDateTime dateTimeOffset::timestamp Added in 6.0 timeSpan.Days floor(date_part('day', timeSpan))::INT timeSpan.Hours floor(date_part('hour', timeSpan))::INT timeSpan.Minutes floor(date_part('minute', timeSpan))::INT timeSpan.Seconds floor(date_part('second', timeSpan))::INT timeSpan.Milliseconds floor(date_part('millisecond', timeSpan))::INT timeSpan.Milliseconds floor(date_part('millisecond', timeSpan))::INT timeSpan.TotalMilliseconds date_part('epoch', interval) / 0.001 Added in 6.0 timeSpan.TotalSeconds date_part('epoch', interval) Added in 6.0 timeSpan.TotalMinutes date_part('epoch', interval) / 60.0 Added in 6.0 timeSpan.TotalDays date_part('epoch', interval) / 86400.0 Added in 6.0 timeSpan.TotalHours date_part('epoch', interval) / 3600.0 Added in 6.0 dateTime1 - dateTime2 dateTime1 - dateTime2 TimeZoneInfo.ConvertTimeBySystemTimeZoneId(utcDateTime, timezone) utcDateTime AT TIME ZONE timezone Added in 6.0, only for timestamptz columns TimeZoneInfo.ConvertTimeToUtc(nonUtcDateTime) nonUtcDateTime::timestamptz Added in 6.0, only for timestamp columns DateTime.SpecifyKind(utcDateTime, DateTimeKind.Unspecified) utcDateTime AT TIME ZONE 'UTC' Added in 6.0, only for timestamptz columns DateTime.SpecifyKind(nonUtcDateTime, DateTimeKind.Utc) nonUtcDateTime AT TIME ZONE 'UTC' Added in 6.0, only for timestamp columns new DateTime(year, month, day) make_date(year, month, day) new DateTime(y, m, d, h, m, s) make_timestamp(y, m, d, h, m, s) new DateTime(y, m, d, h, m, s, kind) make_timestamp or make_timestamptz , based on kind Added in 6.0 EF.Functions.Sum(timespans) sum(timespans) Added in 7.0, see Aggregate functions . EF.Functions.Average(timespans) avg(timespans) Added in 7.0, see Aggregate functions .

Miscellaneous functions

Guid.NewGuid() uuid_generate_v4() , or gen_random_uuid() on PostgreSQL 13 with EF Core 5 and above. nullable.GetValueOrDefault() coalesce(nullable, 0) nullable.GetValueOrDefault(defaultValue) coalesce(nullable, defaultValue)

Binary functions

Notes

See also Aggregate statistics functions .

Row value comparisons

The following allow expressing comparisons over SQL row values . This are particularly useful for implementing efficient pagination, see the EF Core docs for more information.

All of the below were introduced in version 7.0 of the provider.

EF.Functions.GreaterThanOrEqual(ValueTuple.Create(a, b), ValueTuple.Create(c, d)) (a, b) >= (c, d) EF.Functions.LessThanOrEqual(ValueTuple.Create(a, b), ValueTuple.Create(c, d)) (a, b) <= (c, d) ValueTuple.Create(a, b).Equals(ValueTuple.Create(c, d)) (a, b) = (c, d) !ValueTuple.Create(a, b).Equals(ValueTuple.Create(c, d)) (a, b) <> (c, d)

Network functions

As of Npgsql 8.0, IPAddress and NpgsqlCidr are implicitly convertible to NpgsqlInet , and so can be used with the functions below which accept inet .

Trigram functions

The below translations provide functionality for determining the similarity of alphanumeric text based on trigram matching, using the pg_trgm extension which is bundled with standard PostgreSQL distributions. All the below parameters are strings.

Prior to version 6.0, to use these translations, your project must depend on the Npgsql.EntityFrameworkCore.PostgreSQL.Trigrams package, and call UseTrigrams() in your OnModelConfiguring .

LTree functions

The below translations are for working with label trees from the PostgreSQL ltree extension. Use the LTree type to represent ltree and invoke methods on it in EF Core LINQ queries.

LTree support was introduced in version 6.0 of the provider, and requires PostgreSQL 13 or later.

EF.Functions.JsonObjectAgg(tuple_of_2) json_object_agg(tuple_of_2.first, tuple_of_2.second) ranges.RangeAgg() range_agg(ranges) ranges.RangeIntersectAgg() range_intersect_agg(ranges) multiranges.RangeIntersectAgg() range_intersect_agg(multiranges)

Aggregate functions can be used as follows:

var query = ctx.Set<Customer>()
    .GroupBy(c => c.City)
    .Select(
        g => new
            City = g.Key,
            Companies = EF.Functions.ArrayAgg(g.Select(c => c.ContactName))
To use functions accepting a tuple_of_2, project out from the group as follows:
var query = ctx.Set<Customer>()
    .GroupBy(c => c.City)
    .Select(
        g => new
            City = g.Key,
            Companies = EF.Functions.JsonObjectAgg(g.Select(c => ValueTuple.Create(c.CompanyName, c.ContactName)))