 |
VOOZH | about |
|
VOOZH | about |
This document details how LINQ expressions are translated to PostgreSQL-specific SQL expressions in the Npgsql Entity Framework Core Provider. Expression translation is a core component of the query pipeline, transforming .NET logic (method calls, member accesses, and logic operators) into efficient PostgreSQL SQL.
The translation system is primarily composed of method call translators, member translators, and expression visitors. These components work together to intercept .NET expression tree nodes and replace them with SqlExpression nodes representing PostgreSQL functions and operators.
The following diagram illustrates the lifecycle of a LINQ expression as it moves through the Npgsql translation subsystem.
LINQ to PostgreSQL Translation Pipeline
Sources:
Method call translation is handled by classes implementing IMethodCallTranslator. The NpgsqlMethodCallTranslatorProvider coordinates these specialized translators.
NpgsqlStringMethodTranslator maps standard .NET string methods to PostgreSQL equivalents. It also handles EF.Functions extensions like ILike.
| .NET Method | PostgreSQL Translation | Logic Detail |
|---|---|---|
string.IndexOf(s) | strpos(instance, s) - 1 | Adjusts for 1-based indexing src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs97-113 |
string.Replace(o, n) | replace(instance, o, n) | src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs115-130 |
string.Substring(i, l) | substring(instance, i+1, l) | Handles 1-based indexing src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs141-154 |
string.ToLower() | lower(instance) | src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs132-139 |
Enumerable.First(str) | substr(str, 1, 1) | Translates char access src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlStringMethodTranslator.cs58-71 |
Sources:
The provider supports complex translation for DateTime, DateTimeOffset, DateOnly, and TimeOnly through NpgsqlDateTimeMethodTranslator and NpgsqlDateTimeMemberTranslator.
.Year, .Month, and .Day using the PostgreSQL date_part function src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs126-132.AddDays(), .AddHours(), etc., by creating PostgreSQL interval expressions and adding them to the base timestamp src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs47-61DateTime.SpecifyKind by using the PostgreSQL AT TIME ZONE operator src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs70-97Sources:
NpgsqlArrayMethodTranslator handles operations on .NET arrays and List<T>.
Enumerable.ElementAt or array[i] is translated to PostgreSQL array[i+1] (1-based) src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlArrayMethodTranslator.cs33-60Array.IndexOf is translated using array_position src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlArrayMethodTranslator.cs96-115Enumerable.SequenceEqual is translated to the PostgreSQL = operator for array types src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlArrayMethodTranslator.cs62-71Sources:
The NpgsqlFullTextSearchMethodTranslator handles PostgreSQL's advanced text search capabilities. It maps EF.Functions calls to PostgreSQL FTS functions like to_tsvector, plainto_tsquery, and ranking functions ts_rank.
FTS Entity Mapping
Sources:
NpgsqlNetworkTranslator translates methods on IPAddress and PhysicalAddress, supporting PostgreSQL operators for containment (<<, >>) and bitwise operations on inet and cidr types src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlNetworkTranslator.cs14-154NpgsqlRangeTranslator translates properties of NpgsqlRange<T> (like .LowerBound, .UpperBound) and methods for range intersection, union, and overlap src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlRangeTranslator.cs14-146Sources:
The NpgsqlEvaluatableExpressionFilter determines if an expression can be evaluated locally or must be translated to SQL. It explicitly prevents local evaluation of PostgreSQL-specific functions to ensure they are executed on the server.
Key behaviors:
NpgsqlTsQuery.Parse and related FTS extensions src/EFCore.PG/Query/Internal/NpgsqlEvaluatableExpressionFilter.cs45-52Guid.CreateVersion7 when targeting PostgreSQL 18+ to allow the database to generate the UUID src/EFCore.PG/Query/Internal/NpgsqlEvaluatableExpressionFilter.cs53-54ValueTuple.Create to allow translation into SQL row value expressions (x, y) src/EFCore.PG/Query/Internal/NpgsqlEvaluatableExpressionFilter.cs63-69Sources:
When the NodaTime plugin is enabled, additional translators are injected via NpgsqlNodaTimeMemberTranslatorPlugin and NpgsqlNodaTimeMethodCallTranslatorPlugin. These handle NodaTime types like Instant, LocalDate, and Period, mapping them to PostgreSQL's timestamp, date, and interval types.
Sources: