Introduction
Database features are often easy to demonstrate with a small query. The harder question is whether they continue to behave as expected when an application starts using them through an ORM such as Entity Framework Core.
PostgreSQL 19 Beta 3 is particularly interesting for .NET developers because temporal-table functionality can be evaluated from two sides: PostgreSQL's SQL capabilities and EF Core's ability to translate application queries into SQL.
Temporal tables are useful when an application needs to answer questions such as:
What did this record look like yesterday?
When was a value changed?
What was the previous version of a customer's address?
Which values were active during a particular period?
Instead of keeping only the current row, temporal data allows an application to retain historical versions and query them over time.
This article focuses on how to test temporal-table queries with PostgreSQL and EF Core, how to verify the generated SQL, and how to avoid drawing incorrect conclusions from a small benchmark.
What Are Temporal Tables?
A temporal table keeps historical versions of records so an application can query data as it existed at a particular point in time.
Consider a customer record:
Customer
--------
Id
Name
Email
ValidFrom
ValidTo
Suppose the customer initially has:
Email = [email protected]
and later changes it to:
Email = [email protected]
A temporal representation can preserve both versions.
Conceptually:
The application can then ask which version was valid at a particular point in time.
The exact database implementation matters, however. PostgreSQL and SQL Server do not expose temporal functionality in exactly the same way, so EF Core support should not be assumed simply because another relational provider supports a particular temporal API.
Why Test PostgreSQL and EF Core Together?
An application rarely talks directly to PostgreSQL.
A typical .NET application looks more like this:
ASP.NET Core
|
v
EF Core
|
v
Npgsql Provider
|
v
PostgreSQL
A query that looks simple in C# can become significantly more complicated after EF Core translates it into SQL.
That makes integration testing important.
A query can be logically correct in C# but still produce:
The goal is therefore not just to prove that a query returns the expected rows.
The goal is to verify that the entire path behaves correctly.
Creating a PostgreSQL Test Database
For repeatable testing, PostgreSQL can be run locally or in a container.
A simple Docker command is:
docker run --name postgres19-test \
-e POSTGRES_PASSWORD=postgres \
-e POSTGRES_DB=temporal_demo \
-p 5432:5432 \
-d postgres
For a real test environment, pin the exact PostgreSQL image version being evaluated instead of relying on a floating tag.
The application connection string can then be configured as:
{
"ConnectionStrings": {
"DefaultConnection": "Host=localhost;Port=5432;Database=temporal_demo;Username=postgres;Password=postgres"
}
}
Do not use production credentials in a local benchmark or sample application.
Configuring EF Core
A typical ASP.NET Core application can register the PostgreSQL provider through Npgsql:
builder.Services.AddDbContext<AppDbContext>(options =>
{
options.UseNpgsql(
builder.Configuration.GetConnectionString("DefaultConnection"));
});
The model can contain the data required for the temporal test:
public class Customer
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
}
The corresponding context is straightforward:
public class AppDbContext : DbContext
{
public DbSet<Customer> Customers => Set<Customer>();
public AppDbContext(
DbContextOptions<AppDbContext> options)
: base(options)
{
}
}
The important part is that EF Core configuration should match what the PostgreSQL provider actually supports.
Do not copy temporal-table configuration from another EF Core provider and expect it to work unchanged with PostgreSQL.
Modeling Historical Data
A practical approach is to explicitly model historical periods when the application's PostgreSQL design requires them.
For example:
public class CustomerHistory
{
public long Id { get; set; }
public int CustomerId { get; set; }
public string Name { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public DateTime ValidFrom { get; set; }
public DateTime ValidTo { get; set; }
}
This gives the application a clear representation of the historical record.
PostgreSQL's range and temporal-query capabilities can then be used according to the database design.
The important distinction is that temporal behavior should be designed around PostgreSQL's actual features rather than assuming SQL Server's temporal-table model maps one-to-one.
Querying Historical Data
Suppose the application wants to retrieve the customer version that was valid at a specific time.
A query can express the business requirement directly:
var pointInTime = new DateTime(
2026, 8, 26, 10, 30, 0,
DateTimeKind.Utc);
var customer = await db.CustomerHistory
.Where(x =>
x.CustomerId == customerId &&
x.ValidFrom <= pointInTime &&
x.ValidTo > pointInTime)
.SingleOrDefaultAsync();
This translates the business rule into a range check:
ValidFrom <= requested time
AND
ValidTo > requested time
The exact SQL generated by EF Core should be inspected rather than assumed.
Inspecting Generated SQL
EF Core provides ToQueryString() for inspecting the SQL generated by a LINQ query.
For example:
var query = db.CustomerHistory
.Where(x =>
x.CustomerId == customerId &&
x.ValidFrom <= pointInTime &&
x.ValidTo > pointInTime);
Console.WriteLine(query.ToQueryString());
This is extremely useful when investigating temporal queries.
You can verify:
Whether the filter is translated to SQL.
Whether parameters are being used.
Whether unnecessary joins are present.
Whether the expected columns are being selected.
Whether the query can use an appropriate index.
For production applications, SQL logging should be configured carefully because query logs can contain sensitive information.
Checking the PostgreSQL Query Plan
Generated SQL is only half the investigation.
PostgreSQL's query planner determines how that SQL will actually execute.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
"Id",
"CustomerId",
"Name",
"Email",
"ValidFrom",
"ValidTo"
FROM "CustomerHistory"
WHERE "CustomerId" = 101
AND "ValidFrom" <= TIMESTAMPTZ '2026-08-26 10:30:00+00'
AND "ValidTo" > TIMESTAMPTZ '2026-08-26 10:30:00+00';
EXPLAIN shows the execution plan.
ANALYZE executes the query and reports actual execution information.
BUFFERS provides additional information about buffer activity.
Use EXPLAIN ANALYZE carefully against production databases because it executes the query.
Indexing Historical Queries
Temporal queries often filter on both an entity identifier and a time range.
For example:
CREATE INDEX ix_customer_history_customer_valid_from
ON "CustomerHistory" ("CustomerId", "ValidFrom");
Whether this is the best index depends on the actual query patterns and table size.
Do not add indexes simply because a query contains those columns.
Use the PostgreSQL query planner and representative workloads to determine whether an index provides value.
For more advanced temporal workloads, PostgreSQL's range types and specialized indexes may be appropriate. The correct choice depends on how the historical periods are modeled.
Testing With EF Core
A useful integration test should verify the result and the database behavior.
For example:
[Fact]
public async Task Returns_Customer_Version_At_Point_In_Time()
{
var pointInTime = new DateTime(
2026, 8, 26, 10, 30, 0,
DateTimeKind.Utc);
var customer = await db.CustomerHistory
.Where(x =>
x.CustomerId == 101 &&
x.ValidFrom <= pointInTime &&
x.ValidTo > pointInTime)
.SingleOrDefaultAsync();
Assert.NotNull(customer);
Assert.Equal("[email protected]", customer.Email);
}
The test is more useful when the database contains known historical versions.
A realistic integration test should create those versions as part of the test setup rather than depending on data left behind by another test.
Building a Test Matrix
Temporal queries should be tested across different points in time.
| Test Case | Expected Result |
|---|
| Before first version | No record or defined application behavior |
| Inside first period | First version |
| Exactly at transition | New version according to boundary rule |
| Inside second period | Second version |
| After final period | Current/latest behavior |
| Unknown customer | No record |
Boundary conditions are especially important.
For example, if one record ends at 10:30 and another starts at 10:30, the application must have a consistent rule for which record is valid at that exact timestamp.
Common Mistakes
Assuming EF Core Provider Parity
EF Core supports multiple database providers, but provider capabilities are not identical.
A feature supported by SQL Server does not automatically mean the same API or SQL translation exists for PostgreSQL.
Testing Only the LINQ Result
A query can return the correct result while still producing inefficient SQL.
Inspect both generated SQL and the PostgreSQL execution plan.
Ignoring Time Zones
Temporal applications should establish a clear time-zone policy.
Using UTC for stored timestamps is generally easier to reason about for distributed systems.
For example:
DateTime.UtcNow
is preferable to mixing local server time with UTC values.
Testing Only the Happy Path
Temporal queries have important boundary conditions.
Test timestamps before, during, and exactly at transitions.
Creating Indexes Without Measuring
An index can improve one query while increasing storage and write overhead.
Check the execution plan and test with representative data.
Troubleshooting
If EF Core produces unexpected SQL, start by inspecting the LINQ expression and generated query:
Console.WriteLine(query.ToQueryString());
If the SQL looks correct but the query is slow, inspect:
EXPLAIN (ANALYZE, BUFFERS)
If results are incorrect around a transition time, inspect:
If the query works locally but fails in another environment, verify that the PostgreSQL version, Npgsql provider, EF Core version, migrations, and database schema are consistent.
Advantages
Preserves historical versions of data.
Allows applications to answer point-in-time questions.
Makes auditing and historical reporting easier.
Works naturally with EF Core's LINQ querying model when the provider and schema support the required operations.
Provides a strong foundation for testing historical business rules.
Disadvantages
Historical data increases storage requirements.
Temporal queries can become expensive as history grows.
Boundary and time-zone handling require careful design.
Provider-specific database behavior must be considered.
Indexing historical data requires additional planning.
ORM-generated SQL still needs to be inspected for important workloads.
Best Practices
Treat Time as Data
Do not leave timestamp behavior implicit.
Define whether timestamps are UTC, which boundaries are inclusive, and how overlapping periods are handled.
Test Against a Real PostgreSQL Instance
For database-specific functionality, an actual PostgreSQL integration test is more useful than relying exclusively on an in-memory database.
Inspect Generated SQL
When performance or correctness matters, verify what EF Core actually sends to PostgreSQL.
Use Representative Historical Data
A query that performs well against 100 rows may behave differently against millions of historical records.
Validate PostgreSQL and Npgsql Versions
When evaluating a beta database release, keep the exact PostgreSQL, EF Core, and Npgsql versions documented.
This makes test results reproducible and helps separate database behavior from provider behavior.
Conclusion
Temporal data becomes valuable when an application needs to understand not only what is true now, but what was true at a particular point in time.
For .NET developers, PostgreSQL and EF Core provide a useful combination for exploring these scenarios, but the database provider boundary matters. PostgreSQL's temporal and historical-data capabilities should not be assumed to behave exactly like SQL Server's temporal-table implementation.
A solid test starts with a known dataset, a clearly defined time model, and repeatable point-in-time queries. From there, inspect the SQL generated by EF Core and use PostgreSQL's execution plans to understand how the database actually processes the query.
That combination of application-level tests and database-level analysis is much more useful than simply confirming that a LINQ query returns the expected record.