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 SQL 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 a 'migrationBuilder.Sql()' call containing all the necessary Sql-statements to configure the hypertable and its features in TimescaleDB.

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 });
        });

    // SQL generated by CmdScale.EntityFrameworkCore.TimescaleDB.Design
    migrationBuilder.Sql(@"
        SELECT create_hypertable('""DeviceReadings""', 'Time');
        SELECT set_chunk_time_interval('""DeviceReadings""', INTERVAL '1 day');
        ALTER TABLE ""DeviceReadings"" SET (timescaledb.compress = true);
        SET timescaledb.enable_chunk_skipping = 'ON';
        SELECT enable_chunk_skipping('""DeviceReadings""', 'Time');
    ");
}

The tool also correctly generates Down migrations to revert changes, such as modifying a chunk time interval.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        SELECT set_chunk_time_interval('""TradesWithId""', INTERVAL '7 days');
    ");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        SELECT set_chunk_time_interval('""TradesWithId""', INTERVAL '2 day');
    ");
}

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 generates data annotations that represent the hypertable's configuration in 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
        .HasAnnotation("TimescaleDB:ChunkSkipColumns", "Time")
        .HasAnnotation("TimescaleDB:ChunkTimeInterval", "86400000")
        .HasAnnotation("TimescaleDB:EnableCompression", true)
        .HasAnnotation("TimescaleDB:HasReorderPolicy", true)
        .HasAnnotation("TimescaleDB:IsHypertable", true)
        .HasAnnotation("TimescaleDB:ReorderPolicy:IndexName", "DeviceReadings_Time_idx")
        .HasAnnotation("TimescaleDB:ReorderPolicy:ScheduleInterval", "12:00:00")
        .HasAnnotation("TimescaleDB:TimeColumnName", "Time");

    entity.HasIndex(e => e.Time, "DeviceReadings_Time_idx").IsDescending();
});