Skip to content

11.0.md

Latest commit

 

History

History
101 lines (77 loc) · 3.86 KB

11.0.md

File metadata and controls

101 lines (77 loc) · 3.86 KB

11.0 Release Notes

Npgsql.EntityFrameworkCore.PostgreSQL version 11.0 is currently in development. Previews are available on nuget.org.

Support for PostgreSQL 18 temporal constraints

PostgreSQL 18 introduced powerful temporal constraints that allow enforcing data integrity over time periods directly at the database level. The EF Core provider now supports these features, allowing you to define temporal primary keys, unique constraints, and foreign keys.

WITHOUT OVERLAPS for keys

Temporal primary and alternate keys use the WITHOUT OVERLAPS clause to ensure that for any given set of scalar column values, the associated time ranges do not overlap. This is useful for scenarios where you need to track historical data (e.g. employee records, pricing information, equipment assignments) while ensuring data integrity.

For example, an employee can have multiple records in the database (reflecting changes over time), but their validity periods must never overlap:

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
    public NpgsqlRange<DateTime> ValidPeriod { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>(b =>
    {
        b.Property(e => e.ValidPeriod)
            .HasDefaultValueSql("tstzrange(now(), 'infinity', '[)')");

        b.HasKey(e => new { e.EmployeeId, e.ValidPeriod })
            .HasWithoutOverlaps();
    });
}

This generates the following SQL:

CREATE TABLE employees (
    employee_id INTEGER,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    valid_period tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'),
    PRIMARY KEY (employee_id, valid_period WITHOUT OVERLAPS)
);

PERIOD for temporal foreign keys

Temporal foreign keys use the PERIOD clause to ensure that the referenced row exists during the entire time period of the referencing row. This maintains referential integrity across temporal relationships.

For example, when assigning employees to projects, the assignment period must fall within the employee's validity period:

public class ProjectAssignment
{
    public int AssignmentId { get; set; }
    public int EmployeeId { get; set; }
    public string ProjectName { get; set; }
    public NpgsqlRange<DateTime> AssignmentPeriod { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProjectAssignment>(b =>
    {
        b.HasOne<Employee>()
            .WithMany()
            .HasForeignKey(e => new { e.EmployeeId, e.AssignmentPeriod })
            .HasPrincipalKey(e => new { e.EmployeeId, e.ValidPeriod })
            .HasPeriod();
    });
}

This generates the following SQL:

ALTER TABLE project_assignments
ADD CONSTRAINT fk_emp_temporal
FOREIGN KEY (employee_id, PERIOD assignment_period)
REFERENCES employees (employee_id, PERIOD valid_period);

For more details, see the temporal constraints documentation.

Other new features

Breaking changes

  • The provider now assumes at least PostgreSQL 16 by default, taking advantage of newer features (previously, PostgreSQL 14 was targeted). You can configure the targeted PostgreSQL version when configuring your context:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseNpgsql("<connection string>", o => o.SetPostgresVersion(14, 0));