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