Migrating from mssql to postgres in ef6

I was faced with the project of migrating an mssql database to postgres (why? mainly because of licensing fees as we are about to go live with the solution – without any revenues any time soon 🙂 )

This is more a #notetoself if im faced with this again rather than any kind of guideline attempt – please feel free to contact me for details if I can in any way help – I would be happy to share my experience with this.

So, I thought I would document a checklist for those that might stumble across this post while preparing the same action. I was an absolute beginner with postgres when starting this process so some of the comments might feel a bit “verbal”.

The porting took me about 16 hours for a database with around 100 tables, 50 procedures, 50 functions. My process is not applicable for all as I moved the data between the database using json as staging storage for all data – creating a simple import mechanism in c# thought EF6.

  1. Postgres only handles lowercase objects (schemas, table names, columns, procedure names, … ) without quotes. Prepare to rename everything! … unless you want to add a million double quotes to your database procedures and views.
  2. Make sure you read this: https://www.npgsql.org/efcore/modeling/table-column-naming.html before thinking about changing your db entity names in c# (from GolfClub to golf_club) This is mapped automatically by EF6.
  3. Get ready with your favorite text editor with a lot of search-replace regexp.
  4. When putting together dynamic queries and using the pipe || to concatenate strings (instead of + in mssql) make sure you declare empty strings as empty quotes – as when a null is “and-ed” with a string the result is null. I sent quite a few nulls to the execute statement when I was expecting the dynamic string to be full of meaning.

EF6 core – reset migrations during development

In development and while testing various things in EF6 core – I tend to end up with a lot of migration garbage which occasionally I have to take out. This is how it’s done #notetoself

  1. Delete all *. cs files in the Migrations Folder.
  2. Delete the _MigrationHistory Table in the Database
  3. Delete all database tables subject to the migration (take care of the data 🙂 ).
  4. Run dotnet ef migrations add Reset
  5. Run dotnet ef migrations list to get the full name of last migration
  6. Run dotnet ef database update [migration-name]

#notetoself

EF6 core – load grandchildren in query – the key is ThenLoad

Working with entity: Tournament

Which has children: TRound with Course as entity that needs to be loaded based on Id ( as each golf round is played on a Course 🙂 )

ctx.Tournaments.Include(c => c.Trounds).ThenInclude(c => c.Course)

Took me a while to figure out why ctx.Tournaments.Include(c => c.Trounds.Select(c => c.Course)) stopped working, but it is a (good) core thingy.

Blazor OnClick doesn’t fire

#notetoself … Once again, scratching my head for way to long about something that turns out to have a simple workaround #devlife

On one of my (new) blazor pages (/Bookings/TeeTimes), onclick event was not firing. Didn’t matter what kind of flavour of onclick declaration I used, the event was not getting fired.

Until of course I noticed that this page was producing a lot of blazor loading errors:

Turns out I was running the project (as usual) from command line (dotnet) and had a shortcut in my browser to go directly to this page. Seems like that sets the “document root” to “Bookings” and from that it tries to find the blazor files.

Starting from the root page and navigating to the Bookings/TeeTimes page solved the problem, with the permanent fix being adding this within the head element of the layout

<base href="/" />

Docker networking #notetoself

Networking

  • List network setup: docker network ls
  • List: docker network inspect -f '{{range .IPAM.Config}}{{.Subnet}}{{end}}' [networkid]
  • List IPs of all containers.  docker network inspect -f '{{json .Containers}}' [networkid] | jq '.[] | .Name + ":" + .IPv4Address'
  • View host setup: docker exec [nameofnode] cat /etc/hosts
  • Running a named container: docker run -name api1 -p 8080:80 gcp-api

Installing ping in the container: https://stackoverflow.com/questions/39901311/docker-ubuntu-bash-ping-command-not-found
Setting up a network:
https://www.digitalocean.com/community/questions/how-to-ping-docker-container-from-another-container-by-name

Docker networking for Dummies (like me) https://www.freecodecamp.org/news/how-to-get-a-docker-container-ip-address-explained-with-examples/

Classic Setup (web, api, sql)

Setting up the classical environment of having the following 3 components, Web, API, SQL

A few extra notes

A note to self. Use remote command bash when the docker attach command hangs: docker exec -it [container-id] bash

SqlException: Invalid column name GolferId

EF6 Core – reconfiguring navigation properties, the runtime was complaining about a column GolferId being missing (invalid database column).

Took me a minute to figure out and remember the default foreign key dependency naming convention in EF. Sometimes these frameworks are a bit too intelligent for my taste.

Turns out the EF was guessing that if I have a navigation property to a related entity called “User” – the framework will automatically use an object property field called “UserId” :/ … easily solved with the ForeignKey tag 🙂

https://stackoverflow.com/questions/46091157/changing-default-column-name-for-navigation-property