Skip to main content

dotnet ef tools

To integrate TimescaleDB features into your EF Core design-time workflow, such as creating migrations or scaffolding a DbContext, you need the CmdScale.EntityFrameworkCore.TimescaleDB.Design package. This package extends the standard dotnet ef CLI tools to understand and generate TimescaleDB-specific SQL and configurations.

Learn more about EF Core Tools

Installation​

Install the design-time package into your startup project. This is typically your main application project (e.g., an ASP.NET Core Web API).

View on NuGet.org

Install using the .NET CLI:

dotnet add package CmdScale.EntityFrameworkCore.TimescaleDB.Design --version 0.2.0

Code-First: Generating Migrations​

When using the code-first approach, the design package ensures that your hypertable configurations, compression settings, and policies are correctly translated into TimescaleDB migration calls within your migration files.

Usage​

From your project's root directory, run the standard migrations add command. The tooling will automatically generate the TimescaleDB-specific SQL.

dotnet ef migrations add --project <Path to your DataAccess project> --startup-project <Path to your startup project> <MigrationName>

Example​

The generated 'Up' method will contain standard table creation logic, followed by typed migrationBuilder extension calls (such as CreateHypertable) that configure the hypertable and its features. These calls are translated into the required TimescaleDB SQL when the migration is applied.

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Standard EF Core table creation
    migrationBuilder.CreateTable(
        name: "DeviceReadings",
        columns: table => new
        {
            Id = table.Column<Guid>(type: "uuid", nullable: false),
            Time = table.Column<DateTime>(type: "timestamp with time zone", nullable: false),
            DeviceId = table.Column<string>(type: "text", nullable: false),
            Voltage = table.Column<double>(type: "double precision", nullable: false),
            Power = table.Column<double>(type: "double precision", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_DeviceReadings", x => new { x.Id, x.Time });
        });

    // TimescaleDB configuration generated by CmdScale.EntityFrameworkCore.TimescaleDB.Design
    migrationBuilder.CreateHypertable(
        tableName: "DeviceReadings",
        timeColumnName: "Time",
        chunkTimeInterval: "1 day",
        enableCompression: true,
        chunkSkipColumns: ["Time"]);
}

The tool also correctly generates Down migrations to revert changes, such as modifying a chunk time interval. An altered hypertable emits an AlterHypertable call that carries both the new value and the previous one (oldChunkTimeInterval) so the change is fully reversible.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterHypertable(
        tableName: "TradesWithId",
        chunkTimeInterval: "7 days",
        oldChunkTimeInterval: "2 day");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterHypertable(
        tableName: "TradesWithId",
        chunkTimeInterval: "2 day",
        oldChunkTimeInterval: "7 days");
}

DB-First: Scaffolding​

When you are following a DB first approach, you might want to scaffold your DbContext. The CmdScale.EntityFrameworkCore.TimescaleDB.Design package supports this, as well.

Usage​

Use the following command to scaffold the DbContext and entity classes from an existing TimescaleDB database:

dotnet ef dbcontext scaffold
  "Host=localhost;Database=cmdscale-ef-timescaledb;Username=timescale_admin;Password=R#!kro#GP43ra8Ae"
  CmdScale.EntityFrameworkCore.TimescaleDB.Design
  --output-dir Models
  --schema public
  --context-dir .
  --context MyTimescaleDbContext
  --project CmdScale.EntityFrameworkCore.TimescaleDB.Example.DataAccess.DbFirst

This command will:

  • Generate entity models in the Models/ directory
  • Place the MyTimescaleDbContext in the current directory
  • Use the specified connection string to connect to the TimescaleDB instance

⚠️ Note:: When scaffolding your DbContext, use the --schema flag to target only your data schema (usually public). This prevents the tool from unintentionally including TimescaleDB's internal management tables from schemas like _timescaledb_internal.

Example​

The scaffolding process reconstructs the library's Fluent API configuration from the database. This allows your DbContext to be aware of the TimescaleDB features without manual configuration.

modelBuilder.HasPostgresExtension("timescaledb");
modelBuilder.Entity<DeviceReading>(entity =>
{
    entity.HasKey(e => new { e.Id, e.Time });

    entity.IsHypertable(x => x.Time)
        .WithChunkTimeInterval("1 day")
        .EnableCompression()
        .WithChunkSkipping(x => x.Time);
});

With the --data-annotations flag, the configuration is expressed as attributes on the entity classes instead ([Hypertable], [Dimension], [ContinuousAggregate], etc.).

dotnet ef dbcontext scaffold "<connection string>" CmdScale.EntityFrameworkCore.TimescaleDB.Design --data-annotations ...

Scaffolding Limitations​

Some configuration cannot be recovered from the database catalog, and some values come back in a normalized form. A scaffolded model is functionally equivalent to the database, but does not always textually match the hand-written configuration that created it:

  • WithNoData and CreateGroupIndexes are creation-time options of a continuous aggregate and are not queryable from the catalog. They scaffold as their defaults.
  • Implicitly enabled compression is reported as explicit: enabling chunk skipping requires compression, so a hypertable configured only with chunk skipping scaffolds with EnableCompression set.
  • Calendar intervals are stored normalized by TimescaleDB: a chunk interval of "1 month" is stored as a fixed duration and scaffolds as "30 days".
  • Interval values are only humanized when exact: "01:00:00" becomes "1 hour", but a value that does not reduce to a single unit (e.g. "01:30:00") is kept in its raw form rather than being rounded.
  • WHERE clauses come back PostgreSQL-normalized, including casts and parentheses (e.g. "ticker" = 'MCRS' scaffolds as (ticker = 'MCRS'::text)).
  • TimescaleDB's auto-created indexes are suppressed: the descending time index (<table>_<timecol>_idx) and the per-dimension composites (<table>_<dimcol>_<timecol>_idx) are recreated automatically by TimescaleDB and do not scaffold as explicit indexes. A user-defined, non-unique index that exactly matches this name and column pattern is indistinguishable from the auto-created one and is suppressed as well.
  • Raw GROUP BY expressions have no data-annotations representation: in --data-annotations mode, a GROUP BY entry that is not a plain column (e.g. EXTRACT(HOUR FROM time)) is reported as a warning and must be configured via the Fluent API's AddGroupByColumn(...).
  • Unparseable view definitions degrade gracefully: when a continuous aggregate's view SQL cannot be parsed into the structured configuration, a warning is reported and the configuration is preserved as .HasAnnotation(...) calls; migrations recreate the view from the raw SQL definition.

For the full normalization contract and all known limitations, see Scaffolding Behavior and Limitations.