Query Translation

This page provides an overview of how LINQ queries are translated to PostgreSQL SQL in the Npgsql EF Core provider. The translation pipeline converts .NET expression trees into PostgreSQL-specific SQL statements.

For CLR-to-PostgreSQL type mappings, see Type Mapping System. For method-specific translations, see Expression Translation. For SQL rendering, see SQL Generation.

Overview

The query translation pipeline consists of three main stages:

1. Expression Translation: NpgsqlSqlTranslatingExpressionVisitor converts LINQ expression trees into SQL expression nodes.
2. Expression Factory: NpgsqlSqlExpressionFactory creates PostgreSQL-specific SQL expression types.
3. SQL Generation: NpgsqlQuerySqlGenerator renders SQL expression trees into SQL text.

The provider supports PostgreSQL-specific features including arrays, JSON, full-text search, regular expressions, network types, range types, and custom operators.

Query Translation Flow

Sources:

• src/EFCore.PG/Query/Internal/NpgsqlQueryCompilationContext.cs11-18
• src/EFCore.PG/Query/Internal/NpgsqlSqlTranslatingExpressionVisitor.cs20-30
• src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs14-43
• src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs15-32

Core Translation Components

NpgsqlSqlTranslatingExpressionVisitor

The NpgsqlSqlTranslatingExpressionVisitor class extends RelationalSqlTranslatingExpressionVisitor to translate LINQ expressions into SQL expressions. It overrides key visitor methods to handle PostgreSQL-specific translations:

Method	Purpose	Example
VisitConditional	Translates ternary expressions; detects NULLIF patterns	a == b ? null : a → NULLIF(a, b)
VisitUnary	Handles array length, type conversions, and ITuple unwrapping	(ITuple)tuple → unwrapped expression
VisitBinary	Translates binary operations with PostgreSQL operators	Date subtraction → INTERVAL

Sources:

• src/EFCore.PG/Query/Internal/NpgsqlSqlTranslatingExpressionVisitor.cs49-100
• src/EFCore.PG/Query/Internal/NpgsqlSqlTranslatingExpressionVisitor.cs108-132

NpgsqlSqlExpressionFactory

The NpgsqlSqlExpressionFactory creates PostgreSQL-specific SQL expression nodes. It provides factory methods for expressions that don't exist in standard SQL:

• RegexMatch() - Creates PgRegexMatchExpression for ~ operator src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs39-43
• Any() / All() - Creates PgAnyExpression / PgAllExpression for = ANY(array) / LIKE ALL(array) src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs48-61
• ArrayIndex() - Creates PgArrayIndexExpression for array[index] src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs66-80
• JsonTraversal() - Creates PgJsonTraversalExpression for -> / ->> src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs149-154
• ILike() - Creates PgILikeExpression for case-insensitive pattern matching src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs140-144
• AtTimeZone() - Creates AtTimeZoneExpression for timestamp conversion src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs106-134

PostgreSQL Expression Types

The provider defines custom expression types which are visited by the SQL generator:

Sources:

• src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs51-70
• src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs36-226

Method and Member Translators

The provider uses translator providers to register specific .NET method and property translations:

Method Call Translators (via NpgsqlMethodCallTranslatorProvider): Registers translators for string, DateTime, Math, Regex, Json, Arrays, Network, and more src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlMethodCallTranslatorProvider.cs43-67

Member Translators (via NpgsqlMemberTranslatorProvider): Registers translators for members of DateTime, TimeSpan, string, BigInteger, and Range types src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlMemberTranslatorProvider.cs37-49

Type Mapping Integration

The query translation system integrates with NpgsqlTypeMappingSource to ensure correct type handling. The NpgsqlSqlExpressionFactory uses the mapping source to find appropriate store types for expressions like AtTimeZone src/EFCore.PG/Query/NpgsqlSqlExpressionFactory.cs112-127

Translation Examples

String Method Translation

String methods translate to PostgreSQL string functions or pattern matching. For example, Contains can translate to LIKE with wildcards test/EFCore.PG.FunctionalTests/Query/NorthwindMiscellaneousQueryNpgsqlTest.cs25-30

Array Operations

Arrays translate to PostgreSQL native array operations. Contains on a local collection often translates to = ANY (@parameter) test/EFCore.PG.FunctionalTests/Query/NorthwindAggregateOperatorsQueryNpgsqlTest.cs57-60

Operator and Function Mapping

Standard Operator Overrides

The NpgsqlQuerySqlGenerator overrides GetOperator() to translate operators to PostgreSQL equivalents:

Expression Type	Context	PostgreSQL Operator	Notes
Add	String / TsVector / Array	`
ExclusiveOr	Boolean	<>	Logical XOR src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs137
ExclusiveOr	Numeric	#	Bitwise XOR src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs138

Special SQL Constructs

The generator handles PostgreSQL-specific syntax:

• LIMIT/OFFSET: Renders LIMIT and OFFSET clauses src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs89-113
• NULL Ordering: Supports NULLS FIRST / NULLS LAST src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs144-154
• Row Values: Translates ValueTuple and Tuple to row value syntax (a, b) > (c, d) test/EFCore.PG.FunctionalTests/Query/NorthwindWhereQueryNpgsqlTest.cs123-127

PostgreSQL-Specific Features

Row Value Comparison

PostgreSQL supports tuple comparisons. The provider handles these via PgRowValueExpression and NpgsqlSqlNullabilityProcessor, which manages the complex null semantics required for row comparisons src/EFCore.PG/Query/Internal/NpgsqlSqlNullabilityProcessor.cs45-127

Date/Time Translation

Translates components like DayOfWeek using PostgreSQL date_part test/EFCore.PG.FunctionalTests/Query/NorthwindSelectQueryNpgsqlTest.cs17-22 and supports interval arithmetic for AddYears or TimeSpan subtraction test/EFCore.PG.FunctionalTests/Query/NorthwindMiscellaneousQueryNpgsqlTest.cs33-81

Child Pages

For more specific details on the translation pipeline, see:

• Expression Translation — Detailed method call and member translators.
• SQL Generation — Details on the NpgsqlQuerySqlGenerator and SQL rendering.
• Collection Queries — Specifics on array and collection translation (Contains, Any, All).