DOCSEARCHbyAlgolia
Submit Docs

CmdScale Entity Framework Core TimescaleDB

website·Indexed 32 days ago

https://eftdb.cmdscale.com/docs/

A library for integrating TimescaleDB features into EF Core applications. It provides support for hypertables, continuous aggregates, compression policies, retention policies, and reorder policies via Fluent API and data annotations. Includes support for NodaTime time-column types, time-bucket query functions, and UseApacheEdition() configuration.

Tokens
21.3K
Snippets
82
Records
104
Agent score
49%

What's inside CmdScale.EntityFrameworkCore.TimescaleDB

  1. Overview of Hypertable Reorder Policies

    A reorder policy reorganizes data within a hypertable's chunks to match the order of a specific index. This optimization reduces the number of disk pages read during queries that follow the index's order, thereby improving performance. The policy reorders all chunks except for the two most recent ones to avoid interfering with active writes. Only one reorder policy can be applied per hypertable.

  2. Resolve EF Core complex type members in configuration APIs

    The library supports resolving EF Core complex type member references in all column-referencing configuration APIs. This allows developers to use fluent selectors (e.g., x => x.Param1.Value) or dot-separated string paths (e.g., "Param1.Value") to reference properties within a complex type. Resolution honors registered naming conventions; for example, a property Value on complex member Param1 maps to Param1_Value by default, or param1_value when using EFCore.NamingConventions snake_case. Nested complex types (e.g., x => x.Outer.Inner.Value) are resolved recursively.

  3. Understand and use Hypertable Reorder Policies

    A reorder policy reorganizes data within a hypertable's chunks to match the order of a specific index. This reduces the number of disk pages read during queries that follow the index's order, thereby improving performance. The policy reorders all chunks except for the two most recent ones to avoid interfering with active writes. Only one reorder policy can be applied to each hypertable. By default, the optimization job runs every 24 hours, though this is configurable.

  4. Use EF.Functions.TimeBucket for time-series bucketing

    The EF.Functions.TimeBucket method provided by the CmdScale.EntityFrameworkCore.TimescaleDB package partitions time-series rows into fixed-width intervals. This allows for efficient GROUP BY, SELECT, and ORDER BY operations in LINQ queries without requiring raw SQL strings.

  5. Constraints and Requirements for Continuous Aggregates

    When implementing continuous aggregates, adhere to the following constraints:

    • Keyless: Aggregate entities must be marked with the [Keyless] attribute.
    • Source Table: The entity specified in ParentName must be a TimescaleDB hypertable.
    • Attributes: All properties intended for aggregation must use the [Aggregate] attribute with a valid function and source column.
    • Type Safety: Use nameof() when referencing source entity properties to ensure type safety.
    • Infrastructure: The TimescaleDB background worker must be enabled in the database configuration for refresh policies to execute.

  6. Group Continuous Aggregates by additional columns

    Use the [GroupByColumn] attribute on a property to add it to the SQL GROUP BY clause. If no argument is provided, the property name is used as the source column. If the source column in the parent hypertable differs from the property name, pass the source column name as an argument to the attribute.

    Note: Raw SQL GROUP BY expressions (like EXTRACT) are not supported via attributes and must be configured using the Fluent API AddGroupByColumn(string) method.

    [Keyless]
[ContinuousAggregate(MaterializedViewName = "trade_hourly_stats", ParentName = nameof(Trade))]
[TimeBucket("1 hour", nameof(Trade.Timestamp))]
public class TradeHourlyAggregate
{
    // Grouped by the parent's Exchange column
    [GroupByColumn]
    public string Exchange { get; set; } = string.Empty;

    // Grouped by the parent's Ticker column, mapped to 'Symbol' property
    [GroupByColumn(nameof(Trade.Ticker))]
    public string Symbol { get; set; } = string.Empty;

    [Aggregate(EAggregateFunction.Avg, nameof(Trade.Price))]
    public decimal AveragePrice { get; set; }
}

  7. Configure data retention policies for hypertables

    A retention policy automatically drops old chunks from a hypertable or continuous aggregate on a scheduled basis to bound storage consumption. Each hypertable or continuous aggregate supports at most one retention policy. You must specify exactly one of two mutually exclusive drop modes: DropAfter or DropCreatedBefore.

  8. Scaffold DbContext from an existing TimescaleDB database

    Use the dotnet ef dbcontext scaffold command with the CmdScale.EntityFrameworkCore.TimescaleDB.Design provider to generate entity models and a DbContext from an existing TimescaleDB instance.

    Important: Always use the --schema flag (typically --schema public) to target only your data schema. This prevents the tool from scaffolding TimescaleDB's internal management tables from schemas like _timescaledb_internal.

    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

  9. Configure compression for continuous aggregates

    Continuous aggregates support compression policies, but compression must be explicitly enabled on the aggregate first. If a policy is added without enabling compression, an InvalidOperationException is raised during model finalization.
    builder.IsContinuousAggregate<HourlyTradeStat, Trade>(
        "hourly_trade_stats",
        "1 hour",
        x => x.Time)
    .WithCompression()
    .WithCompressionSegmentBy(x => x.Ticker);

builder.WithCompressionPolicy(after: "30 days");

  10. Implement Sparse Indexes on Hypertables

    Sparse indexes (bloom or minmax) allow TimescaleDB to skip chunks based on query predicates.

    Critical Requirement: Sparse indexes require CompressionOrderBy to be configured. If CompressionOrderBy is missing, the convention will throw an InvalidOperationException during model finalization.

    Use the [SparseIndex] attribute to declare index entries.

  11. Implement Hierarchical Continuous Aggregates (Rollup Chains)

    A continuous aggregate can be created from another continuous aggregate instead of a raw hypertable to form a rollup chain (e.g., hourly → daily). This improves performance by reading pre-computed buckets. To implement this, use the .IsContinuousAggregate<TChild, TParentAggregate>() overload where the source type is the parent aggregate entity. The child's time-bucket selector must reference the parent's bucket property.

    To ensure the child can reference the parent's bucket column, you must either:

    1. Designate the parent's bucket property using .WithTimeBucketProperty(x => x.Property).
    2. Map the bucket property to the default column name using .Property(x => x.TimeBucket).HasColumnName("time_bucket").
    // Level 1: hourly aggregate over the raw hypertable
modelBuilder.Entity<TradeHourly>(entity =>
{
    entity.HasNoKey();
    entity.Property(x => x.TimeBucket).HasColumnName("time_bucket");
    entity.IsContinuousAggregate<TradeHourly, Trade>("trade_hourly", "1 hour", x => x.Timestamp)
        .AddAggregateFunction(x => x.AvgPrice, x => x.Price, EAggregateFunction.Avg);
});

// Level 2: daily aggregate whose source is the hourly aggregate
modelBuilder.Entity<TradeDaily>(entity =>
{
    entity.HasNoKey();
    entity.Property(x => x.TimeBucket).HasColumnName("time_bucket");
    entity.IsContinuousAggregate<TradeDaily, TradeHourly>("trade_daily", "1 day", x => x.TimeBucket)
        .AddAggregateFunction(x => x.AvgPrice, x => x.AvgPrice, EAggregateFunction.Avg);
});

  12. Configure a compression policy for a hypertable

    A compression policy automatically converts uncompressed hypertable or continuous aggregate chunks to columnstore format. Before adding a policy, compression must be enabled on the target table using .EnableCompression(), .WithCompressionSegmentBy(), or .WithCompressionOrderBy().

    Each hypertable or continuous aggregate supports at most one compression policy.

    builder.IsHypertable(x => x.Time)
       .WithCompressionSegmentBy(x => x.DeviceId)
       .WithCompressionOrderBy(OrderByBuilder.For<DeviceReading>(x => x.Time).Descending());

builder.WithCompressionPolicy(
    after: "7 days",
    scheduleInterval: "12 hours");