At @Voiteq we’ve started experimenting with Postgres on a greenfield project. We were looking at an elegant way to do ETL from a number of sources into a data warehouse in Postrgres. There seems to be an interesting divide amongst DW/BI (Data Warehouse/Business Intelligence) practioners and more ‘traditional’ developers when it comes to architechting ETL solutions. Tools like SSIS are indeed powerful for the non-coder but the source control and change tracking story isn’t a patch on what’s available when writing straight code.
I was keen to keep as much as possible of the ETL code for this project in C#, for expressiveness, and again, good source control. I knew Entity Framework with PostgreSQL was possible but had never tried it before. Another benefit I was looking for in this approach was the db versioning and upgradability that comes with EF code first migrations.
and it should “just work”.
The next challenge was the tooling. After years in SSMS, PgAdmin III is a bit of a challenge. I don’t want to be dismissive of a FOSS project people have a lot of work in, but the usability needs some help. Anyway, going back to directly querying SQL was not my first choice; @linqpad is far superior! Linqpad doesn’t have native Postgres support but there are a number of 3rd party drivers available. I didn’t have much success with these at first, but eventually solved it, hence this post.
This project on Github looked promising, being updated fairly recently. I installed the driver but couldn’t see the DB which my code first project had deployed. After checking with PgAdmin that it deployed correctly we set about inspecting the code for the postgres linqpad driver. After some time we realised the problem – it is currently limited to querying the default Postgres schema, which is
public in contrast to
dbo in SQL Server. Somewhat unsurprisingly, EF follows this convention, and if not specified it will deploy tables to the
In order to use our setup we’d have to switch the schema to
public with the EF annotations:
[Table(nameof(Operator), Schema = "public")]
public class Operator
With this change it all fell into place, the ease and expressivness of LINQ against PostgreSQL, with all the benefits code first EF brings. As to how this will all work in prod…by