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% | char overload added in 11.0 |
| stringValue.EndsWith(value) | stringValue LIKE '%' || value | char overload added in 11.0 |
| stringValue.FirstOrDefault() | substr(stringValue, 1, 1) | |
| stringValue.IndexOf(value) | strpos(stringValue, value) - 1 | char overload added in 11.0 |
| stringValue.LastOrDefault() | substr(stringValue, length(stringValue), 1) | |
| stringValue.Length | length(stringValue) | |
| stringValue.PadLeft(length) | CASE WHEN length(stringValue) >= length THEN stringValue ELSE lpad(stringValue, length) END | |
| stringValue.PadLeft(length, char) | CASE WHEN length(stringValue) >= length THEN stringValue ELSE lpad(stringValue, length, char) END | |
| stringValue.PadRight(length) | CASE WHEN length(stringValue) >= length THEN stringValue ELSE rpad(stringValue, length) END | |
| stringValue.PadRight(length, char) | CASE WHEN length(stringValue) >= length THEN stringValue ELSE rpad(stringValue, length, char) END | |
| stringValue.Replace(oldValue, newValue) | replace(stringValue, oldValue, newValue) | char overload added in 11.0 |
| stringValue.StartsWith(value) | stringValue LIKE value || '%' | char overload added in 11.0 |
| stringValue.Substring(startIndex, length) | substr(stringValue, startIndex + 1, @length) | |
| stringValue.ToLower() | lower(stringValue) | |
| stringValue.ToString() | stringValue | Null values remain null |
| 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.
Starting with version 11.0, EF.Functions.DateTrunc() supports DateTime, DateTimeOffset and TimeSpan. The optional time zone argument is only supported for values mapped to timestamp with time zone; when omitted for such values, PostgreSQL uses the session time zone.
| .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()) | |
| EF.Functions.DateTrunc("month", value) | date_trunc('month', value) | Added in 11.0 |
| EF.Functions.DateTrunc("month", utcDateTime, "Europe/Berlin") | date_trunc('month', utcDateTime, 'Europe/Berlin') | Added in 11.0; timestamp with time zone only |
| dateTime.AddDays(1) | dateTime + INTERVAL '1 days' | |
| dateTime.AddHours(value) | dateTime + INTERVAL '1 hours' | |
| dateTime.AddMilliseconds(value) (also DateTimeOffset) | dateTime + value * INTERVAL '1 milliseconds' | Added in 11.0 |
| dateTime.AddMicroseconds(value) (also DateTimeOffset) | dateTime + value * INTERVAL '1 microseconds' | Added in 11.0 |
| 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.Millisecond (also DateTimeOffset, TimeOnly) | floor(date_part('milliseconds', dateTime))::INT % 1000 | Added in 11.0 |
| dateTime.Microsecond (also DateTimeOffset, TimeOnly) | date_part('microseconds', dateTime)::INT % 1000 | Added in 11.0 |
| 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.ConvertTimeBySystemTimeZoneId(dateTime, sourceTimeZoneId, destinationTimeZoneId) | (dateTime AT TIME ZONE sourceTimeZoneId) AT TIME ZONE destinationTimeZoneId | Added in 11.0. Requires timestamp without time zone input; returns timestamp without time zone (DateTimeKind.Unspecified), even for UTC destinations. Not supported for DateTimeOffset. |
| 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. |
JSON functions
See JSON mapping for mapping options and PostgreSQL's JSON functions and operators for operator semantics. JSON aggregate functions are listed under Aggregate functions.
| .NET | SQL | Notes |
|---|---|---|
| EF.Functions.JsonContains(json, contained) | json @> contained | jsonb only |
| EF.Functions.JsonContained(contained, json) | contained <@ json | jsonb only |
| EF.Functions.JsonExists(json, key) | json ? key | jsonb only |
| EF.Functions.JsonExistAny(json, key1, key2) | json ?| ARRAY[key1, key2] | jsonb only |
| EF.Functions.JsonExistAll(json, key1, key2) | json ?& ARRAY[key1, key2] | jsonb only |
| EF.Functions.JsonTypeof(json) | jsonb_typeof(json) or json_typeof(json) | Selected according to the mapped column type |
| EF.Functions.JsonPathExists(json, "\(.Age") | JSON_EXISTS(json, '\).Age') | Added in 11.0; requires PostgreSQL 17 or above |
Hstore translations
The following operations are supported for Dictionary<string, string> and ImmutableDictionary<string, string> mapped to PostgreSQL hstore. Operations shown with immutableDictionary apply only to ImmutableDictionary<string, string>.
| .NET | SQL | Notes |
|---|---|---|
| dictionary[key] | dictionary -> key | Added in 11.0 |
| dictionary.GetValueOrDefault(key) | dictionary -> key | Added in 11.0 |
| dictionary.GetValueOrDefault(key, fallback) | CASE WHEN dictionary ? key THEN dictionary -> key ELSE fallback END | Added in 11.0 |
| dictionary.ContainsKey(key) | dictionary ? key | Added in 11.0 |
| dictionary.ContainsValue(value) | array_position(avals(dictionary), value) IS NOT NULL | Added in 11.0 |
| dictionary.Count | cardinality(akeys(dictionary)) | Added in 11.0 |
| immutableDictionary.IsEmpty | cardinality(akeys(immutableDictionary)) = 0 | Added in 11.0 |
| immutableDictionary.Remove(key) | immutableDictionary - key | Added in 11.0 |
| immutableDictionary.RemoveRange(keys) | immutableDictionary - keys | Added in 11.0 |
| immutableDictionary.SetItems(otherDictionary) | immutableDictionary || otherDictionary | Added in 11.0 |
| keys.All(key => dictionary.ContainsKey(key)) | dictionary ?& keys | Added in 11.0 |
| keys.Any(key => dictionary.ContainsKey(key)) | dictionary ?| keys | Added in 11.0 |
Lookups return SQL NULL for missing keys, stored NULL values and NULL hstores, rather than throwing for a missing key. The explicit GetValueOrDefault fallback distinguishes a missing key from a key whose stored value is NULL. Key existence and counts include stored-NULL entries, and ContainsValue(null) finds stored NULL values.
SetItems accepts another hstore-mapped mutable or immutable dictionary, not arbitrary sequences of key/value pairs.
Miscellaneous functions
| .NET | SQL | Notes |
|---|---|---|
| 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) | |
| bool.Parse(stringValue) | CAST(stringValue AS boolean) | Added in 11.0 |
| byte.Parse(stringValue) | CAST(stringValue AS smallint) | Added in 11.0 |
| short.Parse(stringValue) | CAST(stringValue AS smallint) | Added in 11.0 |
| int.Parse(stringValue) | CAST(stringValue AS integer) | Added in 11.0 |
| long.Parse(stringValue) | CAST(stringValue AS bigint) | Added in 11.0 |
| float.Parse(stringValue) | CAST(stringValue AS real) | Added in 11.0 |
| double.Parse(stringValue) | CAST(stringValue AS double precision) | Added in 11.0 |
| decimal.Parse(stringValue) | CAST(stringValue AS numeric) | Added in 11.0 |
Binary functions
| .NET | SQL | Notes |
|---|---|---|
| bytes[i] | get_byte(bytes, i) | |
| bytes.Contains(value) | position(value IN bytes) > 0 | |
| bytes.Any() | length(bytes) > 0 | Added in 11.0 |
| bytes.Concat(bytes2) | bytes || bytes2 | Added in 11.0 |
| bytes.Length | length(@bytes) | |
| bytes1.SequenceEqual(bytes2) | @bytes = @second | |
| bitArray.Get(index) | get_bit(bitArray, index) = 1 | Added in 11.0 |
| bitArray[index] | get_bit(bitArray, index) = 1 | Added in 11.0 |
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 fuzzystrmatch extension which is bundled with standard PostgreSQL distributions.
| .NET | SQL | Notes |
|---|---|---|
| EF.Functions.FuzzyStringMatchSoundex(text) | soundex(text) | |
| EF.Functions.FuzzyStringMatchDifference(source, target) | difference(source, target) | |
| EF.Functions.FuzzyStringMatchDaitchMokotoff(text) | daitch_mokotoff(text) | Added in 11.0. Requires PostgreSQL 16+ |
| 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) |
FuzzyStringMatchDaitchMokotoff returns string[]?, with null possible for inputs without encodable characters, including empty strings. Projection and array indexing are supported, but composing Contains, Any, Intersect(...).Any(), or .Length on the result is not currently translated (dotnet/efcore#35980).
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) |
Cube functions
The below translations are for working with cubes from the PostgreSQL cube extension. Use the NpgsqlCube type to represent cube and invoke methods on it in EF Core LINQ queries.
| .NET | SQL |
|---|---|
| cube1.Overlaps(cube2) | cube1 && cube2 |
| cube1.Contains(cube2) | cube1 @> cube2 |
| cube1.ContainedBy(cube2) | cube1 <@ cube2 |
| cube.NthCoordinate(n) | cube -> n + 1 |
| cube.NthCoordinateKnn(n) | cube ~> n + 1 |
| cube1.Distance(cube2) | cube1 <-> cube2 |
| cube1.DistanceTaxicab(cube2) | cube1 <#> cube2 |
| cube1.DistanceChebyshev(cube2) | cube1 <=> cube2 |
| cube1.Union(cube2) | cube_union(cube1, cube2) |
| cube1.Intersect(cube2) | cube_inter(cube1, cube2) |
| cube.Enlarge(r, n) | cube_enlarge(cube, r, n) |
| cube.Dimensions | cube_dim(cube) |
| cube.IsPoint | cube_is_point(cube) |
| cube.ToSubset(indexes) | cube_subset(cube, indexes + 1) |
| cube.LowerLeft[n] | cube_ll_coord(cube, n + 1) |
| cube.UpperRight[n] | cube_ur_coord(cube, n + 1) |
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) |
| EF.Functions.JsonbObjectAgg(tuple_of_2) | jsonb_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)))
});