# 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

.NET | SQL | Notes
--- | --- | ---
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 |
EF.Functions.ILike(matchExpression, pattern) | matchExpression ILIKE pattern |
EF.Functions.ILike(matchExpression, pattern, escapeCharacter) | matchExpression ILIKE 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 btrim(value, E' \t\n\r') = '' |
stringValue.CompareTo(strB) | CASE WHEN stringValue = strB THEN 0 ... END |
stringValue.Contains(value) | stringValue LIKE %value% |
stringValue.EndsWith(value) | stringValue LIKE '%' || value |
stringValue.FirstOrDefault() | substr(stringValue, 1, 1) |
stringValue.IndexOf(value) | strpos(stringValue, value) - 1 |
stringValue.LastOrDefault() | substr(stringValue, length(stringValue), 1) |
stringValue.Length | length(stringValue) |
stringValue.PadLeft(length) | lpad(stringValue, length) |
stringValue.PadLeft(length, char) | lpad(stringValue, length, char) |
stringValue.PadRight(length) | rpad(stringValue, length) |
stringValue.PadRight(length, char) | rpad(stringValue, length, char) |
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() | btrim(stringValue) |
stringValue.Trim(trimChar) | btrim(stringValue, trimChar) |
stringValue.TrimEnd() | rtrim(stringValue) |
stringValue.TrimEnd(trimChar) | rtrim(stringValue, trimChar) |
stringValue.TrimStart() | ltrim(stringValue) |
stringValue.TrimStart(trimChar) | ltrim(stringValue, trimChar) |
EF.Functions.Reverse(value) | reverse(value) |
Regex.IsMatch(stringValue, "^A+") | stringValue ~ '^A+' (with options) |
Regex.IsMatch(stringValue, "^A+", regexOptions) | stringValue ~ '^A+' (with options) |
string.Join(", ", a, b) | concat_ws(', ', a, b) |
string.Join(", ", array) | array_to_string(array, ', ', '') |
string.Join(", ", agg_strings) | string_agg(agg_strings, ', ') | See Aggregate functions.
EF.Functions.StringToArray(s, "|") | string_agg(s, '|') |
EF.Functions.StringToArray(s, "|", "FOO") | string_agg(s, '|', 'FOO') |

## Date and time functions

##### Note

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.

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.

.NET | SQL | Notes
--- | --- | ---
DateTime.UtcNow (6.0+) | now() |
DateTime.Now (6.0+) | now()::timestamp |
DateTime.Today (6.0+) | date_trunc('day', now()::timestamp) |
DateTime.UtcNow (legacy) | now() AT TIME ZONE 'UTC' |
DateTime.Now (legacy) | now() |
DateTime.Today (legacy) | date_trunc('day', now()) |
dateTime.AddDays(1) | dateTime + INTERVAL '1 days' |
dateTime.AddHours(value) | dateTime + INTERVAL '1 hours' |
dateTime.AddMinutes(1) | dateTime + INTERVAL '1 minutes' |
dateTime.AddMonths(1) | dateTime + INTERVAL '1 months' |
dateTime.AddSeconds(1) | dateTime + INTERVAL '1 seconds' |
dateTime.AddYears(1) | dateTime + INTERVAL '1 years' |
dateTime.Date | date_trunc('day', dateTime) |
dateTime.Day | date_part('day', dateTime)::INT |
dateTime.DayOfWeek | floor(date_part('dow', dateTime))::INT |
dateTime.DayOfYear | date_part('doy', dateTime)::INT |
dateTime.Hour | date_part('hour', dateTime)::INT |
dateTime.Minute | date_part('minute', dateTime)::INT |
dateTime.Month | date_part('month', dateTime)::INT |
dateTime.Second | date_part('second', dateTime)::INT |
dateTime.Year | date_part('year', dateTime)::INT |
dateTime.ToUniversalTime | dateTime::timestamptz |
dateTime.ToLocalTime | dateTime::timestamp |
dateOnly.DayNumber | dateOnly - DATE '0001-01-01' | Added in 9.0
DateOnly.FromDayNumber(x) | DATE '0001-01-01' + x | Added in 9.0
dateOnly1.DayNumber - dateOnly2.DayNumber | dateOnly1 - dateOnly2 | Added in 9.0
dateTimeOffset.DateTime | dateTimeOffset AT TIME ZONE 'UTC' |
dateTimeOffset.UtcDateTime | No PG operation (.NET-side conversion from DateTimeOffset to DateTime only) |
dateTimeOffset.LocalDateTime | dateTimeOffset::timestamp |
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 |
timeSpan.TotalSeconds | date_part('epoch', interval) |
timeSpan.TotalMinutes | date_part('epoch', interval) / 60.0 |
timeSpan.TotalDays | date_part('epoch', interval) / 86400.0 |
timeSpan.TotalHours | date_part('epoch', interval) / 3600.0 |
dateTime1 - dateTime2 | dateTime1 - dateTime2 |
TimeZoneInfo.ConvertTimeBySystemTimeZoneId(utcDateTime, timezone) | utcDateTime AT TIME ZONE timezone | Only for timestamptz columns
TimeZoneInfo.ConvertTimeToUtc(nonUtcDateTime) | nonUtcDateTime::timestamptz | Only for timestamp columns
DateTime.SpecifyKind(utcDateTime, DateTimeKind.Unspecified) | utcDateTime AT TIME ZONE 'UTC' | Only for timestamptz columns
DateTime.SpecifyKind(nonUtcDateTime, DateTimeKind.Utc) | nonUtcDateTime AT TIME ZONE 'UTC' | 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` |
EF.Functions.Sum(timespans) | sum(timespans) | See Aggregate functions.
EF.Functions.Average(timespans) | avg(timespans) | See Aggregate functions.

## Miscellaneous functions

.NET | SQL
--- | ---
collection.Contains(item) | item IN collection
enumValue.HasFlag(flag) | enumValue & flag = flag
Guid.CreateVersion7() | uuidv7() (added in 10 when targeting PG18, client-evaluated otherwise)
Guid.NewGuid() | gen_random_uuid(), or uuid_generate_v4() from `uuid-ossp` when targeting pre-PG13
nullable.GetValueOrDefault() | coalesce(nullable, 0)
nullable.GetValueOrDefault(defaultValue) | coalesce(nullable, defaultValue)

## Binary functions

.NET | SQL | Notes
--- | --- | ---
bytes[i] | get_byte(bytes, i) |
bytes.Contains(value) | position(value IN bytes) > 0 |
bytes.Length | length(@bytes) |
bytes1.SequenceEqual(bytes2) | @bytes = @second |

## Math functions

.NET | SQL | Notes
--- | --- | ---
Math.Abs(value) | abs(value) |
Math.Acos(d) | acos(d) |
Math.Asin(d) | asin(d) |
Math.Atan(d) | atan(d) |
Math.Atan2(y, x) | atan2(y, x) |
Math.Ceiling(d) | ceiling(d) |
Math.Cos(d) | cos(d) |
Math.Exp(d) | exp(d) |
Math.Floor(d) | floor(d) |
Math.Log(d) | ln(d) |
Math.Log10(d) | log(d) |
Math.Max(x, y) | greatest(x, y) |
Math.Min(x, y) | least(x, y) |
Math.Pow(x, y) | power(x, y) |
Math.Round(d) | round(d) |
Math.Round(d, decimals) | round(d, decimals) |
Math.Sin(a) | sin(a) |
Math.Sign(value) | sign(value)::int |
Math.Sqrt(d) | sqrt(d) |
Math.Tan(a) | tan(a) |
Math.Truncate(d) | trunc(d) |
EF.Functions.Random() | random() |

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.

.NET | SQL
--- | ---
EF.Functions.GreaterThan(ValueTuple.Create(a, b), ValueTuple.Create(c, d)) | (a, b) > (c, d)
EF.Functions.LessThan(ValueTuple.Create(a, b), ValueTuple.Create(c, d)) | (a, b) < (c, d)
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

.NET | SQL
--- | ---
IPAddress.Parse(string) | CAST(string AS inet)
PhysicalAddress.Parse(string) | CAST(string AS macaddr)
EF.Functions.LessThan(net1, net2) | net1 < net2
EF.Functions.LessThanOrEqual(net1, net2) | net1 <= net2
EF.Functions.GreaterThan(net1, net2) | net1 > net2
EF.Functions.GreaterThanOrEqual(net1, net2) | net1 >= net2
EF.Functions.ContainedBy(inet1, inet2) | inet1 << inet2
EF.Functions.ContainedByOrEqual(inet1, inet2) | inet1 <<= inet2
EF.Functions.Contains(inet1, inet2) | inet1 >> inet2
EF.Functions.ContainsOrEqual(inet1, inet2) | inet1 >>= inet2
EF.Functions.ContainsOrContainedBy(inet1, inet2) | inet1 && inet2
EF.Functions.BitwiseNot(net) | ~net1
EF.Functions.BitwiseAnd(net1, net2) | net1 & net2
EF.Functions.BitwiseOr(net1, net2) | net1 | net2
EF.Functions.Add(inet, int) | inet + int
EF.Functions.Subtract(inet, int) | inet - int
EF.Functions.Subtract(inet1, inet2) | inet1 - inet2
EF.Functions.Abbreviate(inet) | abbrev(inet)
EF.Functions.Abbreviate(cidr) | abbrev(cidr)
EF.Functions.Broadcast(inet) | broadcast(inet)
EF.Functions.Family(inet) | family(inet)
EF.Functions.Host(inet) | host(inet)
EF.Functions.HostMark(inet) | hostmask(inet)
EF.Functions.MaskLength(inet) | masklen(inet)
EF.Functions.Netmask(inet) | netmask(inet)
EF.Functions.Network(inet) | network(inet)
EF.Functions.SetMaskLength(inet) | set_masklen(inet)
EF.Functions.SetMaskLength(cidr) | set_masklen(cidr)
EF.Functions.Text(inet) | text(inet)
EF.Functions.SameFamily(inet1, inet2) | inet_same_family(inet1, inet2)
EF.Functions.Merge(inet1, inet2) | inet_merge(inet1, inet2)
EF.Functions.Truncate(macaddr) | trunc(macaddr)
EF.Functions.Set7BitMac8(macaddr8) | macaddr8_set7bit(macaddr8)

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

.NET | SQL
--- | ---
EF.Functions.TrigramsShow(s) | show_trgm(s)
EF.Functions.TrigramsSimilarity(s1, s2) | similarity(s1, s2)
EF.Functions.TrigramsWordSimilarity(s1, s2) | word_similarity(s1, s2)
EF.Functions.TrigramsStrictWordSimilarity(s1, s2) | strict_word_similarity(s1, s2)
EF.Functions.TrigramsAreSimilar(s1, s2) | s1 % s2
EF.Functions.TrigramsAreWordSimilar(s1, s2) | s1 <% s2
EF.Functions.TrigramsAreNotWordSimilar(s1, s2) | s1 %> s2
EF.Functions.TrigramsAreStrictWordSimilar(s1, s2) | s1 <<% s2
EF.Functions.TrigramsAreNotStrictWordSimilar(s1, s2) | s1 %>> s2
EF.Functions.TrigramsSimilarityDistance(s1, s2) | s1 <-> s2
EF.Functions.TrigramsWordSimilarityDistance(s1, s2) | s1 <<-> s2
EF.Functions.TrigramsWordSimilarityDistanceInverted(s1, s2) | s1 <->> s2
EF.Functions.TrigramsStrictWordSimilarityDistance(s1, s2) | s1 <<<-> s2
EF.Functions.TrigramsStrictWordSimilarityDistanceInverted(s1, s2) | s1 <->>> s2

## Fuzzy string match functions

The below translations provide functionality for determining similarities and distance between strings, using the `pg_trgm` extension which is bundled with standard PostgreSQL distributions.

.NET | SQL
--- | ---
EF.Functions.FuzzyStringMatchSoundex(text) | soundex(text)
EF.Functions.FuzzyStringMatchDifference(source, target) | difference(source, target)
EF.Functions.FuzzyStringMatchLevenshtein(source, target) | levenshtein(source, target)
EF.Functions.FuzzyStringMatchLevenshtein(source, target, ins_cost, del_cost, sub_cost) | levenshtein(source, target, ins_cost, del_cost, sub_cost)
EF.Functions.FuzzyStringMatchLevenshteinLessEqual(source, target, max_d) | levenshtein(source, target, max_d)
EF.Functions.FuzzyStringMatchLevenshteinLessEqual(source, target, ins_cost, del_cost, sub_cost, max_d) | levenshtein(source, target, ins_cost, del_cost, sub_cost, max_d)
EF.Functions.FuzzyStringMatchMetaphone(text, max_output_length) | metaphone(text, max_output_length)
EF.Functions.FuzzyStringMatchDoubleMetaphone(text) | dmetaphone(text)
EF.Functions.FuzzyStringMatchDoubleMetaphoneAlt(text) | dmetaphone_alt(text)

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

.NET | SQL
--- | ---
ltree1.IsAncestorOf(ltree2) | ltree1 @> ltree2
ltree1.IsDescendantOf(ltree2) | ltree1 <@ ltree2
ltree.MatchesLQuery(lquery) | ltree ~ lquery
ltree.MatchesLTxtQuery(ltxtquery) | ltree @ ltxtquery
lqueries.Any(q => ltree.MatchesLQuery(q)) | ltree ? lqueries
ltrees.Any(t => t.IsAncestorOf(ltree)) | ltrees @> ltree
ltrees.Any(t => t.IsDescendantOf(ltree)) | ltrees <@ ltree
ltrees.Any(t => t.MatchesLQuery(lquery)) | ltrees ~ ltree
ltrees.Any(t => t.MatchesLTxtQuery(ltxtquery)) | ltrees @ ltxtquery
ltrees.Any(t => lqueries.Any(q => t.MatchesLQuery(q))) | ltrees ? lqueries
ltrees.FirstOrDefault(l => l.IsAncestorOf(ltree)) | ltrees ?@> ltree
ltrees.FirstOrDefault(l => l.IsDescendantOf(ltree)) | ltrees ?<@ ltree
ltrees.FirstOrDefault(l => l.MatchesLQuery(lquery)) | ltrees ?~ ltree
ltrees.FirstOrDefault(l => l.MatchesLTxtQuery(ltxtquery)) | ltrees ?@ ltree
ltree.Subtree(0, 1) | subltree(ltree, 0, 1)
ltree.Subpath(0, 1) | sublpath(ltree, 0, 1)
ltree.Subpath(2) | sublpath(ltree, 2)
ltree.NLevel | nlevel(ltree)
ltree.Index(subpath) | index(ltree, subpath)
ltree.Index(subpath, 2) | index(ltree, subpath, 2)
LTree.LongestCommonAncestor(ltree1, ltree2) | lca(index(ltree1, ltree2)

## Aggregate functions

The PostgreSQL aggregate functions are documented here.

.NET | SQL
--- | ---
string.Join(", ", agg_strings) | string_agg(agg_strings, ', ')
EF.Functions.ArrayAgg(values) | array_agg(values)
EF.Functions.JsonbAgg(values) | jsonb_agg(values)
EF.Functions.JsonAgg(values) | json_agg(values)
EF.Functions.Sum(timespans) | sum(timespans)
EF.Functions.Average(timespans) | avg(timespans)
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)
EF.Functions.StandardDeviationSample(values) | stddev_samp(values)
EF.Functions.StandardDeviationPopulation(values) | stddev_pop(values)
EF.Functions.VarianceSample(values) | var_samp(values)
EF.Functions.VariancePopulation(values) | var_pop(values)
EF.Functions.Correlation(tuple) | corr(tuple_of_2.first, tuple_of_2.second)
EF.Functions.CovariancePopulation(tuple) | covar_pop(tuple_of_2.first, tuple_of_2.second)
EF.Functions.CovarianceSample(tuple) | covar_samp(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrAverageX(tuple) | regr_avgx(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrAverageY(tuple) | regr_avgy(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrCount(tuple) | regr_count(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrIntercept(tuple) | regr_intercept(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrR2(tuple) | regr_r2(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrSlope(tuple) | regr_slope(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrSXX(tuple) | regr_sxx(tuple_of_2.first, tuple_of_2.second)
EF.Functions.RegrSXY(tuple) | regr_sxy(tuple_of_2.first, tuple_of_2.second)

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)))
            });