Timescale DB with EF Core

Continuation to previous timescale db on edge story, am continuing the same implementation on the EF Core. Please find the points below. Most of the EF Core functionality will be similar even in the case of Time Scale DB also as its an extension over Postgres DB

install “Npgsql.EntityFrameworkCore.PostgreSQL” using Nuget

Now we have the Postgres EEF Core is available for us. Now Lets Register the context

Here the IServiceCollection will get added with the Database Context based on the Connection string “dbConnection”.

Now we can create the model for our table, we will add the model here. we will use the same table structure from the previous story. But we will add “Id” column on the same to add complexity.

Now we have the model ready. Now we need to create a hyper table in the migration process. Postgres EF Core will create only normal postgres table using the migration. Once we are in the Migration process. we need to first run the Timescale extension on the database and and also create hyper table for our table.

For the same we will create a new extension with attribute

Lets discuss the code above

Below code block will create a custom Attribute for our hyper table and this will be decorated on the timestamp column for partitioning.

Now lets add this attribute to our previous entity class, after the change, class will look like below.

Now the function which will create hyper tables on the table classes which is having the previous attribute

as a next step we will create timescale extension on the database

This will convert our normal postgres db to timescale db.

Now We will loop through all the Entity types in the context and check for the entity which is having “HypertableColumn” attribute in the property as a decoration. and will create hypertables on teach table.

as our table name is having both capital and small letter in it, we need to use the table name inside single and double quotes like ‘“TableName”’. Below code have the same implementation.

Now we have the migration scripts ready to proceed.

But when we run the same we will get error as we using “ID” column in our table and we are partitioning with timestamp column. To solve the same you can choose any following methods based on your convenience

  1. Specify no key and remove “Id” column.

In some IoT scenarios, there will be less relevance to the Id column, so we can remove the column and specify “HasNoKey()” in the Entity.

2. Specify Composite Column

As an alternate solution you can create composite key with “Id” and “Timestamp” column like below

Now we are ready to run the migration on the same

here we will apply hyper tables extension on the context after the regular migration. This will create hyper tables

Incase if we don’t have the data context and it need to be taken from the serice collection, you can use the below code also

Now we can insert the data to our TimeScale DB from the Ef Core repository.

Please note : If you are using the azure Postgress on cloud, you need to be sure that the Postgress is configured with the timescale extension.

please follow the steps to convert your azure Postgres to timescale DB

Then restart your potgress

Then use your favourate tool like “Azure Data Studio” to connect to the Postgress and run below command to make the database capable to work with timescale extension

now you are good and then try connecting to the Postgress on cloud..

Am an IoT developer interested in microsoft technologies and cloud technologies