Chapter 13: .NET: A Measured Approach

Microsoft's "Modeling for Performance" documentation for Entity Framework Core mentions materialized views by name. It notes that in PostgreSQL, materialized views must be manually refreshed, "typically done via a timer." When the vendor's guidance and this book's thesis align, one feels reassured that the advice is sound.

The .NET ecosystem provides three clean pathways to materialized views: EF Core's keyless entities for teams already using the ORM, Dapper's lightweight mapping for those who prefer explicit SQL, and direct Npgsql for infrastructure code that needs no object mapping at all. ASP.NET Core's BackgroundService handles scheduled refresh with no additional packages. This chapter covers all three.

The Keyless Entity

Entity Framework Core maps materialized views through keyless entities — entities configured with HasNoKey() that EF Core treats as read-only by design.

The entity class

public class DashboardMetrics
{
    public DateOnly Day { get; set; }
    public int Events { get; set; }
    public int UniqueUsers { get; set; }
    public decimal Revenue { get; set; }
}

No [Key] attribute. No Id property. No base class. The entity is a plain C# class — all configuration happens in the DbContext.

The DbContext configuration

public class AppDbContext : DbContext
{
    public DbSet<DashboardMetrics> DashboardMetrics => Set<DashboardMetrics>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DashboardMetrics>(entity =>
        {
            entity.HasNoKey();
            entity.ToView("mv_dashboard");

            entity.Property(e => e.Day).HasColumnName("day");
            entity.Property(e => e.Events).HasColumnName("events");
            entity.Property(e => e.UniqueUsers).HasColumnName("unique_users");
            entity.Property(e => e.Revenue)
                .HasColumnName("revenue")
                .HasColumnType("numeric(13,2)");
        });
    }
}

Two method calls carry the weight. HasNoKey() tells EF Core this entity has no primary key — and the consequences are immediate: EF Core will not generate INSERT, UPDATE, or DELETE statements for it. Attempting to Add(), Update(), or Remove() a keyless entity throws InvalidOperationException. This is the strongest write-prevention in any ORM covered in this book — not a silent ignore (Hibernate's @Immutable), not a model event returning false (Laravel's booted()), but a hard exception. The operation is impossible, not merely discouraged.

ToView("mv_dashboard") maps the entity to the materialized view's name and tells EF Core migrations to leave it alone entirely. No CREATE TABLE. No ALTER. No DROP. EF Core knows this object is managed externally.

The column mapping via HasColumnName() bridges C# naming conventions (PascalCase) to PostgreSQL conventions (snake_case). The Npgsql EF Core provider also supports a global snake_case naming convention if you prefer to avoid per-property configuration.

Querying

var metrics = await context.DashboardMetrics
    .AsNoTracking()
    .Where(m => m.Day >= DateOnly.FromDateTime(DateTime.UtcNow.AddDays(-30)))
    .OrderByDescending(m => m.Day)
    .Take(30)
    .ToListAsync();

Standard LINQ. Where(), OrderBy(), GroupBy(), Select(), Sum(), Count() — all translate to PostgreSQL SQL through the Npgsql provider. AsNoTracking() is technically redundant for keyless entities (EF Core already skips change tracking), but it documents intent explicitly and provides a measurable performance benefit when applied to keyed entities mapped to materialized views.

// Aggregation
var totalRevenue = await context.DashboardMetrics
    .Where(m => m.Day >= DateOnly.FromDateTime(DateTime.UtcNow.AddDays(-30)))
    .SumAsync(m => m.Revenue);

// Projection
var summary = await context.DashboardMetrics
    .OrderByDescending(m => m.Day)
    .Select(m => new { m.Day, m.Events })
    .Take(7)
    .ToListAsync();

The materialized view is a table. LINQ queries it as a table. The EF Core query pipeline translates LINQ to SQL. PostgreSQL serves the pre-computed results.

The Migration

EF Core has no dedicated API for creating views or materialized views. Microsoft's recommendation is direct: create an empty migration and add the view definition via raw SQL.

dotnet ef migrations add CreateDashboardMaterializedView

Edit the generated migration:

public partial class CreateDashboardMaterializedView : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE MATERIALIZED VIEW mv_dashboard AS
            SELECT
                date_trunc('day', created_at)::date AS day,
                COUNT(*)::integer AS events,
                COUNT(DISTINCT user_id)::integer AS unique_users,
                SUM(CASE WHEN event_type = 'purchase'
                    THEN amount ELSE 0 END)::numeric(13,2) AS revenue
            FROM events
            WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
            GROUP BY 1
            WITH DATA;

            CREATE UNIQUE INDEX idx_mv_dashboard_day ON mv_dashboard (day);
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            "DROP MATERIALIZED VIEW IF EXISTS mv_dashboard CASCADE"
        );
    }
}

migrationBuilder.Sql() executes raw SQL within the migration transaction. The Down() method enables rollback — apply it with dotnet ef database update <PreviousMigrationName> to clean up. Because ToView() was used in the entity configuration, EF Core will not generate any additional migration SQL for this entity — it understands the object is managed by your explicit migration, not by EF Core's schema diff.

When the materialized view's definition changes, create a new migration with DROP CASCADE + CREATE — the same pattern as every other framework, since PostgreSQL has no CREATE OR REPLACE MATERIALIZED VIEW.

Scheduled Refresh with BackgroundService

ASP.NET Core's BackgroundService provides built-in infrastructure for periodic background tasks. Combined with PeriodicTimer (.NET 6+), it creates a clean async refresh loop with no external dependencies.

public class MaterializedViewRefreshService : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;
    private readonly ILogger<MaterializedViewRefreshService> _logger;
    private readonly TimeSpan _interval = TimeSpan.FromMinutes(15);

    public MaterializedViewRefreshService(
        IServiceScopeFactory scopeFactory,
        ILogger<MaterializedViewRefreshService> logger)
    {
        _scopeFactory = scopeFactory;
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var timer = new PeriodicTimer(_interval);

        while (await timer.WaitForNextTickAsync(stoppingToken))
        {
            try
            {
                using var scope = _scopeFactory.CreateScope();
                var context = scope.ServiceProvider
                    .GetRequiredService<AppDbContext>();

                await context.Database.ExecuteSqlRawAsync(
                    "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard",
                    stoppingToken
                );

                _logger.LogInformation("Dashboard MV refreshed successfully");
            }
            catch (OperationCanceledException)
            {
                // Application shutting down — expected
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Dashboard MV refresh failed");
            }
        }
    }
}

Registration:

// Program.cs
builder.Services.AddHostedService<MaterializedViewRefreshService>();

One line. The service starts when the application starts, stops when the application stops, and respects the CancellationToken for graceful shutdown.

The IServiceScopeFactory deserves explanation: DbContext is registered as a scoped service in ASP.NET Core's dependency injection container, but BackgroundService is a singleton. You cannot inject a scoped service into a singleton directly — the DI container will throw at startup. IServiceScopeFactory creates a new scope per refresh iteration, providing a fresh DbContext each time. This is a well-documented .NET pattern, but it catches developers who haven't built hosted services before.

PeriodicTimer is the modern replacement for Task.Delay loops and System.Threading.Timer. It doesn't drift, it's async-native, and it integrates cleanly with CancellationToken. Available from .NET 6 onward.

Multi-instance: advisory lock

For Kubernetes deployments or multiple Azure App Service instances:

protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
    using var timer = new PeriodicTimer(_interval);

    while (await timer.WaitForNextTickAsync(stoppingToken))
    {
        using var scope = _scopeFactory.CreateScope();
        var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        var conn = context.Database.GetDbConnection();
        await conn.OpenAsync(stoppingToken);

        try
        {
            await using var lockCmd = conn.CreateCommand();
            lockCmd.CommandText =
                "SELECT pg_try_advisory_lock(hashtext('mv_dashboard'))";
            var acquired = (bool)(await lockCmd.ExecuteScalarAsync(stoppingToken))!;
            if (!acquired) continue;

            try
            {
                await using var refreshCmd = conn.CreateCommand();
                refreshCmd.CommandText =
                    "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard";
                await refreshCmd.ExecuteNonQueryAsync(stoppingToken);
                _logger.LogInformation("Dashboard MV refreshed");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Refresh failed");
            }
            finally
            {
                await using var unlockCmd = conn.CreateCommand();
                unlockCmd.CommandText =
                    "SELECT pg_advisory_unlock(hashtext('mv_dashboard'))";
                await unlockCmd.ExecuteScalarAsync(stoppingToken);
            }
        }
        finally
        {
            await conn.CloseAsync();
        }
    }
}

The explicit GetDbConnection() with OpenAsync is deliberate — session-level advisory locks are bound to the connection, so the lock, the refresh, and the unlock must execute on the same one. EF Core's ExecuteSqlRawAsync does not guarantee connection reuse between calls.

And pg_cron, as always, eliminates the C# code entirely.

Dapper and Direct Npgsql

For developers who prefer lightweight data access — less ceremony, more control.

Dapper

using var connection = new NpgsqlConnection(connectionString);

// Query — Dapper maps columns to properties by convention
var metrics = await connection.QueryAsync<DashboardMetrics>(
    @"SELECT day, events, unique_users AS UniqueUsers, revenue
      FROM mv_dashboard
      ORDER BY day DESC
      LIMIT @Limit",
    new { Limit = 30 }
);

// Refresh
await connection.ExecuteAsync(
    "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard"
);

Two calls. No DbContext, no model configuration, no OnModelCreating. Dapper maps result columns to properties by name — the AS UniqueUsers alias handles the snake_case-to-PascalCase conversion. For teams that found Chapter 12's Go approach appealing but work in .NET, Dapper provides the thinnest possible layer over SQL.

Direct Npgsql

var dataSource = NpgsqlDataSource.Create(connectionString);

// Refresh
await using var cmd = dataSource.CreateCommand(
    "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard"
);
await cmd.ExecuteNonQueryAsync();

// Query
await using var readCmd = dataSource.CreateCommand(
    "SELECT * FROM mv_dashboard ORDER BY day DESC LIMIT 30"
);
await using var reader = await readCmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    var day = reader.GetFieldValue<DateOnly>(0);
    var events = reader.GetInt32(1);
    // ...
}

NpgsqlDataSource (.NET 7+) manages connection pooling internally. No ORM. No mapping library. Pure ADO.NET with Npgsql's PostgreSQL-specific features. This level of directness is appropriate for infrastructure code — refresh services, health checks, monitoring — where object mapping adds overhead without value.

When to use which: EF Core for teams already committed to it — the keyless entity pattern integrates materialized views into the existing data access layer cleanly. Dapper for read-heavy services that prioritize query control. Direct Npgsql for infrastructure code that operates at the SQL level.

.NET provides three clean pathways to materialized views, each suited to a different architectural preference. EF Core's keyless entities offer the strongest write-prevention of any ORM in this book. BackgroundService integrates refresh into the hosting model with a single line of registration. And Microsoft's own performance documentation recommends the approach by name.

This book's thesis is not contrarian. It is, increasingly, the consensus.

The framework chapters are complete. In Part IV, we turn to the scaling challenges that materialized views help you defer — serverless connection storms, the question everyone asks about sharding, and the connection pooler landscape you will need to navigate.