Hello Alex,
thanks for your reply. I will try to elaborate the problem.
Say, I create a migration which contains the following step:
Code: Select all
AddForeignKey("SCHEMA.TABLE_A", new[] { "COLUMN_A", "COLUMN_B" }, "SCHEMA.TABLE_B", new[] { "COLUMN_A", "COLUMN_B" }, cascadeDelete: true);
During the execution of the migration, EF6 comes up with a name, like "SCHEMA.TABLE_A.COLUMN_A_COLUMN_B" (or something similar). This is too long for my fancy Oracle Database, so dotconnect will replace it with "FK_<Hash>". The hash is calculated with "SCHEMA.TABLE_A.COLUMN_A_COLUMN_B".GetHashCode().
The problem is, that .GetHashCode is only guaranteed to create the same hash within the current app-domain. In .Net Core, if you restart the program (rerun the migration) the hash code will be different.
So when I run this exact migration three times, you might get:
Code: Select all
ALTER TABLE ARIANE_SCHEMA.TABLE_A
ADD CONSTRAINT FK_000000000 FOREIGN KEY (COLUMN_A, COLUMN_B) REFERENCES SCHEMA.TABLE_B (COLUMN_A, COLUMN_B)
ON DELETE CASCADE
ALTER TABLE ARIANE_SCHEMA.TABLE_A
ADD CONSTRAINT FK_111111111 FOREIGN KEY (COLUMN_A, COLUMN_B) REFERENCES SCHEMA.TABLE_B (COLUMN_A, COLUMN_B)
ON DELETE CASCADE
ALTER TABLE ARIANE_SCHEMA.TABLE_A
ADD CONSTRAINT FK_222222222 FOREIGN KEY (COLUMN_A, COLUMN_B) REFERENCES SCHEMA.TABLE_B (COLUMN_A, COLUMN_B)
ON DELETE CASCADE
When I run the Down migration, I might end up with
Code: Select all
ALTER TABLE ARIANE_SCHEMA.TABLE_A DROP CONSTRAINT FK_1003657290
which will fail, since the foreign key was generated with a different name.
If you run the script on multiple databases, you end up with a different foreign key for each system, which makes further migrations very cumbersome.
Over the last years, we kept having problems with not matching foreign keys, which probably came from .Net Framework upgrades, but we never investigated that in depth. Now, with .Net Core the issue is more apparent, since a new hash value is returned for every run.
The failing migrations are especially annoying on Oracle, where migrations cannot be done in transactions, as far as I know.
So now I have multiple databases with random strings as foreign keys. How can this issue be solved?
The only good idea, I came up with, is that I create a list of all foreign keys used in the database. From that i can create a lookup table, like "SCHEMA.TABLE_A.COLUMN_A_COLUMN_B" -> "FK_1003657290". If you could provide a callback to calculate the truncated name, I could use my lookup table to return the correct key. It would be great to also get the connection string (or maybe even a database connection), so I can adapt the returned value for every instance.
In case a new foreign key is created, I could return a stable hash.
What do you think? Can you implement something like this?
Do you have a better idea?