3

I want to use TimescaleDB on a specific table containing time-series data. The question I have is how to setup time-series from entity framework core and make a specific table a hyper table?

Database: Postgress version 11 Framework: Ef Core 2.2 Method: Code First

Timon Post
  • 2,779
  • 1
  • 17
  • 32
  • 1
    Do you create create your schema manually, or you let Entity create it for you? If you create it yourself, and Entity just connects to the database, then you can simply execute `SELECT create_hypertable('table_name'...)` and Entity will have no problem working with 'table_name'. If Entity creates your db schema, then you can create a custom database initializer, and after the db is created, execute a raw query `SELECT create_hypertable...` – Blagoj Atanasovski Aug 27 '19 at 10:30
  • I create my scheme manually, with models, and do a migration from there. I will have a look at the database initializer, thanks for the tip! – Timon Post Aug 27 '19 at 15:12
  • @TimonPost any idea how to do this in ef core since it doesnt have database initializers? – psclkhoury Jun 14 '20 at 14:33
  • 1
    @psclkhoury you should have: 1) one date-time field in your model. 2) In postgress db you should install the timescale plugin with `CREATE EXTENSION timescaledb;` SQL command. 3) Call `dbcontext.Database.ExecuteSqlCommand("SELECT create_hypertable('device_points', 'Datetime');");` on database creation. – Timon Post Jun 14 '20 at 19:17

1 Answers1

5

This is the solution for code-first EF. It is pretty straight forward.

1) Add one date-time field in your model of the table you want to scale with timescale db.

2) Install the timescale plugin for Postgres.

CREATE EXTENSION timescaledb;

3) Execute the following SQL in a database seeder or initializer. 

dbcontext.Database.ExecuteSqlCommand("SELECT create_hypertable('table_name', 'datetime_field_name');");

(datetime_field_name is a required datetime field for timescale db (see 1))

Timon Post
  • 2,779
  • 1
  • 17
  • 32