We looked a little deeper after scaffolding another test database, which worked. It turns out that all of the foreign keys in the target database had disappeared. Our Dotnet6 application still worked without these as they were already in the dbContext model.

After re-defining all of the keys everything works as it should.

Thanks to Gert Arnold for suggesting to look at the database schema as we were unaware of the missing keys.