Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Tue 28 Sep 2021 15:10

Hello,

I am using Devart.Data.Oracle.EF6 9.14.1273 with EntityFramework 6.4.4 running on .Net 5. I use code first migrations with Oracle 12C. Since it has a 30 char limitation, I use

Code: Select all

            var config = OracleEntityProviderConfig.Instance;
            config.CodeFirstOptions.TruncateLongDefaultNames = true;
I face some trouble when dropping foreign keys. The SQL generated often does not match the foreign keys in the database.

But today I realized that the problem is deeper: When I generate a sql script using the -script option, I end up with a new constraint name on every run:

Code: Select all

ALTER TABLE ARIANE_ADMIN.REPORT_VERSION_TO_TARGET_APP
  ADD CONSTRAINT FK_N181954248 FOREIGN KEY (BOX_ID, BOX_VERSION_NO) REFERENCES ARIANE_ADMIN.REPORT_VERSIONS (BOX_ID, BOX_VERSION_NO)
  ON DELETE CASCADE

Code: Select all

ALTER TABLE ARIANE_ADMIN.REPORT_VERSION_TO_TARGET_APP
  ADD CONSTRAINT FK_N1552352547 FOREIGN KEY (BOX_ID, BOX_VERSION_NO) REFERENCES ARIANE_ADMIN.REPORT_VERSIONS (BOX_ID, BOX_VERSION_NO)
  ON DELETE CASCADE

Code: Select all

ALTER TABLE ARIANE_ADMIN.REPORT_VERSION_TO_TARGET_APP
  ADD CONSTRAINT FK_393048484 FOREIGN KEY (BOX_ID, BOX_VERSION_NO) REFERENCES ARIANE_ADMIN.REPORT_VERSIONS (BOX_ID, BOX_VERSION_NO)
  ON DELETE CASCADE
I could not find a way to explicitly specify a name for the foreign key.

This is blocking me at the moment, since I have a setup that is relying on the migrations to work. Do you have any solution or workaround for this?

Any help is welcome
Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Wed 29 Sep 2021 10:34

Hello,

I tried to understand that issue in the hope of finding a workaround.

I think I found the root cause in Devart.Common.Entity.d3.a(string). It reads like you are using string.GetHashCode() to generate the truncated identifier.

However, string.GetHashCode() is not guaranteed to be stable and generated hashes should never be used outside of the application domain in which they were created.

The implementation changed between .Net Framework versions and in .Net Core a different seed is used for every app domain.

In my eyes, this is a very serious issue, since it breaks any existing project with code first migrations.

Is there a way to circumvent this issue?
Is there a possibility to specify foreign key names manually, like by setting an attribute?

I would appreciate any idea that can help me to get my migrations running again.

Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Wed 29 Sep 2021 18:21

I keep thinking on how this could be fixed. One idea I came up with is to allow us to register a callback that is used to create the shortened identifier. You could then provide a default implementation returning a stable hash function.

For existing keys, we could manually create a lookup table (maybe that can be created from the database) and continue to use our messed up database version.

Maybe you come up with a better idea.

Kind regards
Peter

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by Shalex » Thu 30 Sep 2021 08:35

Thank you for sharing your findings.
PeterUser wrote: Wed 29 Sep 2021 10:34 I think I found the root cause in Devart.Common.Entity.d3.a(string). It reads like you are using string.GetHashCode() to generate the truncated identifier.
1. We will investigate the question and update our implementation of config.CodeFirstOptions.TruncateLongDefaultNames in the package https://www.nuget.org/packages/devart.data.oracle.ef6.
PeterUser wrote: Tue 28 Sep 2021 15:10 I face some trouble when dropping foreign keys. The SQL generated often does not match the foreign keys in the database.
2. If you create the database objects with Code-First Migrations, the Down method of the generated migration includes DropForeignKey. Does it fail in your case? Please describe your scenario.

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Fri 01 Oct 2021 06:18

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?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by Shalex » Mon 04 Oct 2021 11:38

PeterUser wrote: Fri 01 Oct 2021 06:18 So now I have multiple databases with random strings as foreign keys. How can this issue be solved?
1. After the fix, the generated hash for the particular FK name will be constant to solve the issue for the newly created FKs.

2. We will look for a solution for deleting the existing FKs created with string.GetHashCode().

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Tue 05 Oct 2021 08:29

Hallo Alex,

that is good news.

Do you already have an estimate of the release date for of the fix? I know that you cannot always plan software, but it will help me to see how much effort I should put into workarounds.

Do you have an proposal how to fix the deletion issue?

Kind regards
Peter

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by Shalex » Thu 07 Oct 2021 14:03

PeterUser wrote: Tue 05 Oct 2021 08:29 Do you already have an estimate of the release date for of the fix? I know that you cannot always plan software, but it will help me to see how much effort I should put into workarounds.
1. We are going to implement the fix in several days and provide the internal build of dotConnect for Oracle to you. The release date is not yet scheduled.
PeterUser wrote: Tue 05 Oct 2021 08:29 Do you have an proposal how to fix the deletion issue?
2. The investigation is in progress. As soon as we have any results, we will notify you.

ZiporaTilda
Posts: 1
Joined: Mon 11 Oct 2021 19:08

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by ZiporaTilda » Mon 11 Oct 2021 19:14

I have always suck on this issue but you have put it so well, I must enjoy now

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by Shalex » Fri 15 Oct 2021 17:38

We have implemented the following fixes:
  • The implementation of config.CodeFirstOptions.TruncateLongDefaultNames in EF6 .NET Standard compliant assemblies is improved: the generated names are constant, independent on a current domain and workstation
  • The config.CodeFirstOptions.DropConstraintsByColumns configuration option is added (default value is False) to provide the possibility of dropping the existing foreign keys and indexes basing on the column names instead of constraint names
The internal build with the fixes will be provided to you via email.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by Shalex » Fri 22 Oct 2021 16:53

New build of dotConnect for Oracle 9.14.1369 is available for download now: viewtopic.php?f=1&t=48942.

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Mon 25 Oct 2021 09:07

Hello Alex,

thank you. I tried the new version and it works. DropConstraintsByColumns is actually a pretty cool solution - I tend to solve too many problems in C#.

One minor thing, though: This makes the issue with the broken script generation with -script more acute. Maybe you can take a look at that.

Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Mon 25 Oct 2021 09:09

Hello Peter,
thanks a lot for your feedback. I’m glad that we could help.

Usually, this kind bug should not have happened, since e have automated tests in place for such critical and fragile features, but, you know, there was this funny incident where one of our employees brought her dog to work and it has so much fun chasing all those test down the aisle and – well l, long story short: We rewrote the tests and since we finally figured out how to run our software on .net 6 (thanks for reporting that), we directly tested it there.

Please excuse that you had to dig through obfuscated code to find the cause of this issue, but our CFO thinks that we can squeeze some extra bugs out of customers who like to get involved to improve the product. Maybe you can convince your boss to pay for that. Then you might figure out why we still use “dbo” instead of the correct schema name for migrations from time to time. I’m really curious where this is coming from.

We will add the documentation for “DropConstraintsByColumns” as soon as possible and your proposal to add XML documentation to all configuration objects is a good idea. We will check that out.

Thanks for getting involved and kind regards
Your devart team
No problem, you're welcome.

Kind regards
Peter

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by Shalex » Thu 28 Oct 2021 15:21

PeterUser wrote: Mon 25 Oct 2021 09:07 One minor thing, though: This makes the issue with the broken script generation with -script more acute. Maybe you can take a look at that.
The issue is fixed. Refer to viewtopic.php?f=30&t=47840.

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Script generation broken for EF 6.4.4 with dotconnect for Oracle part II

Post by PeterUser » Sat 30 Oct 2021 15:14

Hello Alex,

sorry, I didn't check carefully. It is indeed partly fixed.

All the

Code: Select all

DECLARE
  BINARY_VALUE BLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(BINARY_VALUE, TRUE);
  DBMS_LOB.APPEND(BINARY_VALUE, TO_BLOB(CAST('XXX' AS LONG RAW)));
  ...
block is indeed written directly to the script file. Thanks for that.

However, the new generated script for dropping the constraint is still mostly in the output:
sql file:

Code: Select all


BEGIN
/

Console output:

Code: Select all

Applying explicit migration: 202110301503399_Test.
  FOR c IN (
    SELECT INDEX_OWNER, INDEX_NAME
      FROM SYS.ALL_IND_COLUMNS
    WHERE TABLE_OWNER = 'ARIANE_ADMIN' AND TABLE_NAME = 'BOX_LOTS' AND COLUMN_NAME IN ('BOX_ID', 'BOX_VERSION_NO', 'ID')
    HAVING COUNT(*) = 3
    GROUP BY INDEX_OWNER, INDEX_NAME
  )
  LOOP
    SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT(
      'DROP INDEX "' || c.INDEX_OWNER || '"."' || c.INDEX_NAME || '"'
    );
  END LOOP;
END;

Can you please check if this can also be written to the sql file?

Thank you
Peter

Post Reply