Most of the applications we build store the current state of things. A customer record holds today’s address, an order table holds today’s status, and once you update a row, the old value is gone for good. This works well for the majority of business systems, but it falls apart the moment you need to look at how a value changed over time.
That is where time series data comes in. If you are building anything that ingests sensor readings, stock prices, application metrics, or any other value that only matters in the context of when it was recorded, a plain relational table starts to show its limits pretty quickly. In this article I want to walk through combining PostgreSQL, the TimescaleDB extension, and Entity Framework Core to store and query time series data without leaving the .NET ecosystem you already know.
Why time series storage is different
Before touching any code, it helps to place time series among the common data storage patterns. Current state management, which is what EF Core developers use by default, stores the latest value for each entity. You lose the history of how that value arrived unless you build change tracking yourself.
Event sourcing is one answer to that problem. Instead of storing state, you store every action a user took as an event, and you rebuild the current state by replaying those events. It gives you a full audit trail, but building an event sourced system properly is a fair amount of extra engineering, and logical ordering matters more here than strict time intervals.
Time series sits in a different spot again. Here the timestamp is not incidental, it is the whole point. Every row matters because of when it happened, and you are usually dealing with a high volume of inserts and queries that slice data by interval, such as the last hour, the last week, or a rolling thirty day window.
Time series databases earn their keep in a few common areas: infrastructure and application monitoring, anomaly detection, forecasting, and general trend analysis where you need to look back over history rather than just check today’s number. If your data model has a timestamp as one of its most important columns and you expect a large volume of writes, it is worth evaluating a time series approach before you commit to a plain table design.
Where TimescaleDB fits in
TimescaleDB is an open source extension for PostgreSQL, built by Timescale Inc, that adds table structures and SQL functions purpose built for time series data. Rather than asking you to run a completely separate database engine, it builds on top of Postgres, so your existing backup strategy, monitoring, and admin tooling continue to work unchanged.
This matters for .NET teams for a practical reason. Npgsql already gives you a mature PostgreSQL driver, and Npgsql.EntityFrameworkCore.PostgreSQL is a first class EF Core provider that covers most of what you would expect from EF Core. You are not bolting on some unfamiliar client library, you are extending a stack that already works well with .NET.
The combination works nicely once it is wired up, but there are a few EF Core specific quirks worth knowing before you start modeling your entities. I will walk through them as we go.
Spinning up TimescaleDB with Docker
The fastest way to get a TimescaleDB instance running locally is the official Docker image. You do not need to install Postgres separately, the image ships with the extension already enabled.
docker pull timescale/timescaledb-ha:pg14-latest
docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg14The first command pulls the image, the second starts a container named timescaledb, exposes Postgres on the default port 5432, and sets a password through an environment variable. Once the container is running, you can connect with any Postgres client, psql, pgAdmin, DataGrip, or the database tools built into JetBrains Rider all work fine. For anything beyond local experimentation, swap the plain text password for a secret pulled from your configuration provider or a vault, since environment variables show up in shell history and container inspect output.
Understanding hypertables
The core concept in TimescaleDB is the hypertable. A hypertable looks like a normal table to your queries, but internally it is partitioned into chunks based on a time interval, one day by default. TimescaleDB creates and manages these chunks automatically as you insert data, so from an application code perspective you do not need to think about partitioning at all.
The detail that trips up most EF Core developers is that hypertables typically do not have a primary key. Data is inserted and then queried using aggregate functions rather than looked up and updated by key. That directly conflicts with how EF Core normally expects to work, since the framework wants a key on every tracked entity. We will deal with this using EF Core’s keyless entity feature.
You can still add regular indexes to a hypertable to speed up queries, and all your usual Postgres admin habits, vacuum, analyze, backup, continue to apply. That is really the selling point here, you get time series specific performance without giving up the operational tooling your team already knows.
Setting up the .NET project
Create a console application and install the EF Core CLI tooling if you have not already. The commands below scaffold a new project and set up a local tool manifest for dotnet-ef.
dotnet new console -o HelloTimescale && cd HelloTimescale
dotnet new tool-manifest
dotnet tool install dotnet-efYou will also need two NuGet packages, Microsoft.EntityFrameworkCore.Design for the migration tooling, and Npgsql.EntityFrameworkCore.PostgreSQL for the actual PostgreSQL provider. With those in place, you are ready to model your entities.
Modeling keyless entities
Since a hypertable has no primary key, EF Core needs to be told to treat the entity as keyless. A keyless entity comes with a few restrictions worth knowing before you commit to this pattern: it is never change tracked, so you cannot insert, update, or delete through the DbContext, it supports only a subset of navigation mapping, it cannot be mapped between two keyless entities, and it can be backed by a defining query similar to a DbSet.
For this example we will model two entities, Stock, which represents a row in the hypertable, and Company, a normal keyed table that Stock references.
[Keyless]
public class Stock
{
public DateTimeOffset Time { get; set; }
[ForeignKey(nameof(Company))]
public string Symbol { get; set; } = "";
public decimal? Price { get; set; }
public int? DayVolume { get; set; }
public Company Company { get; set; } = default!;
}
public class Company
{
[Key] public string Symbol { get; set; } = "";
public string Name { get; set; } = "";
}The [Keyless] attribute on Stock tells EF Core not to expect a primary key on this entity. Company stays a normal entity with Symbol as its key, and Stock references it through Symbol as a foreign key for navigation purposes only, not for referential integrity enforcement inside the hypertable itself.
Register both as DbSet properties on your DbContext.
public DbSet<Stock> Stocks { get; set; } = default!;
public DbSet<Company> Companies { get; set; } = default!;Remember that Stocks is read only from the DbContext’s point of view. If you try to add or update a Stock instance through the context, EF Core throws, because keyless entities are not change tracked. For inserting time series data at any real volume, plain ADO.NET or a dedicated bulk copy path such as Npgsql’s binary COPY API will serve you far better than routing writes through EF Core anyway.
Turning the table into a hypertable via migration
Generate your first migration as usual.
dotnet ef migrations add InitialEF Core generates a standard CREATE TABLE migration for Stocks and Companies. That alone gives you a regular Postgres table, not a hypertable, so you need to edit the generated migration and add a call to TimescaleDB’s create_hypertable function at the end of the Up method.
// Convert Stocks Table to Hypertable
// language=sql
migrationBuilder.Sql(
"SELECT create_hypertable( '\"Stocks\"', 'Time');\n" +
"CREATE INDEX ix_symbol_time ON \"Stocks\" (\"Symbol\", \"Time\" DESC)"
);The create_hypertable call converts the Stocks table into a hypertable partitioned on the Time column. The index on Symbol and Time descending matters more than it looks, since a hypertable starts out with no unique index at all once you strip out the primary key, so query performance on common filters like a specific stock symbol over a time range depends entirely on the indexes you add yourself.
Querying with raw SQL and intervals
With the hypertable in place, you can already run interval based queries using FromSqlRaw or FromSqlInterpolated.
var sql = """
SELECT * FROM "Stocks"
WHERE "Time" > now() - INTERVAL '1 week'
AND "Symbol" = 'MSFT'
""";
var trades = db.Stocks.FromSqlRaw(sql).Count();
Console.WriteLine($"{trades} trades of MSFT in the last week");This counts how many MSFT trades landed in the Stocks hypertable over the last week. The INTERVAL keyword is a TimescaleDB and Postgres convenience that makes relative time filtering read naturally in SQL, instead of computing a DateTime offset in C# first. The catch becomes obvious once you have more than one or two of these queries scattered through your codebase, you end up with raw SQL strings spread across the project, which is exactly what EF Core is usually meant to help you avoid.
Wrapping SQL in EF Core database functions
EF Core’s database function mapping feature lets you expose a Postgres function as a strongly typed method on your DbContext, so callers do not need to know or care that raw SQL is involved underneath. Here is what the call site looks like once it is set up.
// UTC Only
// Read this first https://www.npgsql.org/doc/types/datetime.html
var date = new DateTime(2022, 06, 29, 0, 0, 0, DateTimeKind.Utc);
var top = db
.GetWeeklyResults(date)
.First(x => x.Symbol == "MSFT");
Console.WriteLine($"{top.Name} ({top.Symbol}): {top.Start:C} - {top.End:C} ~{top.Average:C}");This reads like any other LINQ query against the DbContext, which is the whole point. Note the comment about UTC, Npgsql is strict about DateTimeKind when mapping to timestamp with time zone columns, and passing a DateTime with Kind set to Unspecified or Local will either throw or silently produce the wrong result depending on your Npgsql version. Always normalize to UTC before calling into Postgres time functions.
To make this method available, first define a keyless record to hold the result shape.
[Keyless]
public record IntervalResult(
string Symbol,
string Name,
decimal Start,
decimal End,
decimal Average);IntervalResult is keyless for the same reason Stock was, it represents a projection of aggregated data rather than a row you would ever track or update.
Next, add the method to your DbContext.
public IQueryable<IntervalResult> GetWeeklyResults(DateTime value)
{
if (value.Kind != DateTimeKind.Utc) {
// https://www.npgsql.org/doc/types/datetime.html
throw new ArgumentException("DateTime.Kind must be of UTC to convert to timestamp with time zone");
}
return FromExpression(() => GetWeeklyResults(value));
}The explicit Kind check up front saves you from a confusing runtime error deep inside Npgsql, and it fails fast with a message that actually tells you what went wrong. FromExpression is what wires this C# method to the underlying database function call when EF Core translates the query.
Then register the mapping in OnModelCreating.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// shouldn't be used since we have a method
modelBuilder
.HasDbFunction(typeof(StocksDbContext).GetMethod(nameof(GetWeeklyResults), new[] { typeof(DateTime) })!)
.HasName("get_weekly_results")
.IsBuiltIn(false);
}HasDbFunction tells EF Core that calls to GetWeeklyResults should translate into a call to the Postgres function named get_weekly_results, rather than being evaluated in memory. Setting IsBuiltIn to false is important, it tells EF Core this is a user defined function it needs to create through a migration, not something already available in Postgres.
The function itself does not exist in the database yet, so generate a new migration for it.
dotnet ef migrations add AddGetWeeklyResultsFunctionEF Core scaffolds an empty migration since it has no built in way to generate the SQL body of a Postgres function. You need to write that part yourself inside the Up and Down methods.
public partial class AddGetWeeklyResultsFunction : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
var function = $"""
create or replace FUNCTION get_weekly_results("value" timestamp with time zone)
returns Table
(
"Symbol" text,
"Name" text,
"Start" numeric,
"End" numeric,
"Average" numeric
)
LANGUAGE SQL
as
$func$
SELECT srt."Symbol",
C."Name",
first("Price", "Time") as "Start",
last("Price", "Time") as "End",
avg("Price") as "Average"
FROM "Stocks" srt
inner join "Companies" C on C."Symbol" = srt."Symbol"
WHERE "Time" > "value" - INTERVAL '1 week'
AND "Time" <= "value"
GROUP BY srt."Symbol", "Name"
ORDER BY "End" DESC;
$func$;
""";
migrationBuilder.Sql(function);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("drop function get_weekly_results(timestamp with time zone);");
}
}The function uses TimescaleDB’s first and last aggregate functions, which return the value of one column ordered by another, exactly what you need to get the opening and closing price within a week without writing a window function by hand. The Down method drops the function cleanly, which matters if you ever need to roll the migration back in a lower environment.
This approach takes more upfront work than inline raw SQL, but it pays off on any project that lives longer than a few months. Parameter validation happens in C# before the database is even called, the query shows up as a normal LINQ call at every use site, and you are not hunting for SQL strings scattered across unrelated files when you need to change the logic. The trade-off is that you now have SQL function bodies living inside migration files, which is its own kind of scattering, so pick whichever approach fits your team’s habits and stick to it consistently.
Putting it all together
With both approaches wired up, the full console program looks like this.
using Microsoft.EntityFrameworkCore;
using TimescaleSample.Models;
var db = new StocksDbContext();
var sql = """
SELECT * FROM "Stocks"
WHERE "Time" > now() - INTERVAL '1 week'
AND "Symbol" = 'MSFT'
""";
var trades = db.Stocks.FromSqlRaw(sql).Count();
Console.WriteLine($"{trades} trades of MSFT in the last week");
var date = new DateTime(2022, 06, 29, 0, 0, 0, DateTimeKind.Utc);
var top = db
.GetWeeklyResults(date)
.First(x => x.Symbol == "MSFT");
Console.WriteLine($"{top.Name} ({top.Symbol}): {top.Start:C} - {top.End:C} ~{top.Average:C}");Running this against seeded sample data produces output similar to the following.
50892 trades of MSFT in the last week
Microsoft (MSFT): $266.39 - $256.65 ~$259.77The first line comes from the raw SQL count query, the second from the database function call. Both approaches read data correctly, the difference is entirely about how maintainable the query stays inside a larger codebase over time.
Practical considerations before you adopt this
A few things are worth thinking through before you bring TimescaleDB into a production system. Since hypertables are keyless from EF Core’s perspective, you lose the usual change tracking convenience, which means your ingestion path needs a separate strategy, typically raw ADO.NET, Npgsql’s binary COPY for bulk loads, or a background worker that batches writes outside of EF Core entirely.
Retention and downsampling need a plan from day one. TimescaleDB gives you retention policies and continuous aggregates to automatically drop old chunks or pre-compute rollups, but neither of those is something EF Core manages for you, you configure them directly in Postgres. Skipping this step is the most common reason teams see a hypertable grow unexpectedly large within a few months of going live.
If your team already runs Postgres in production, this is a low friction way to add time series capability without introducing a brand new database engine, a new set of credentials, and a new operational runbook. If you do not already run Postgres, weigh that operational cost against something like Azure Data Explorer or a managed time series service, since standing up Postgres purely to get TimescaleDB is a bigger decision than it looks on paper.
EF Core’s support here is workable but clearly secondary to its normal current state use case. You will find yourself dropping into raw SQL and hand written migrations more often than in a typical CRUD project. That is a reasonable trade for the query ergonomics TimescaleDB and Postgres give you, but go in expecting it rather than being surprised by it.
Leave a Reply