-
Notifications
You must be signed in to change notification settings - Fork 223
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Cannot scaffold if two indices exist on same column #228
Comments
Can you please provide a minimal SQL schema dump of your PostgreSQL which reproduces this problem? |
Also, I notice that in your dependencies you have Npgsql.EntityFrameworkCore.PostgreSQL.Design - this package no longer exists and isn't required (this is why there isn't a 2.0.0 RTM version of it). Please remove this dependency, it may be causing the issue. |
Dependency removed but the exception is the same |
Problem found. I changed the code excluding long name indexes and scaffold-dbcontext succeeded. After the run I found another problem that I dont understand, but may be caused by excluded indexes:
is generated as
abcod, CHARACTER(2) created as char, instead with version 1.1.1 was correctly a string. |
I have a minimal repro: CREATE TABLE "user" (
"id" BIGSERIAL PRIMARY KEY,
"name" TEXT,
"email" TEXT NOT NULL UNIQUE,
"password" TEXT NOT NULL
);
CREATE INDEX user_email_idx ON "user" ("email"); This was my bad, I thought I needed an additional index on email to make lookups easier. I ended up with two indexes, user_email_key and user_email_idx. They were causing the duplicate exception. |
@jamiepenney this should not be, your indexes have different names, annotation is by name. I've a lot of tables with more than one index that works fine, but I'm getting this exception only with this, that after scaffolding contains this 4 indexes
Source table is
p.s: no comment on the benefit of some indexes |
# Real problem
and this throw the exception
|
I think the problem is in EFCore and not here. Precisely in EFCore.Design project
Seems to be that EFCore check the existing of an index relying on his own properties, not considering their direction (ASC or DESC).
but in EFCore this information is lost. In 1.1.1 version, index with identical properties were discarded, and only the last was taken (tested). |
I made a quick fix to my cloned code, so I can scaffold my existing database. I added a method to check if the DatabaseTable already contains an index with same fields, and I log a warning to the PM console. Check
Call and warning
Another simplest way could be to exclude duplicated indexes directly into SQL query, but I prefer the "warning way" so people can check the usefulness of their indexes. |
My apologies for spamming but I also have troubles trying to scrafod db.
And from Package Manager Console I am trying to
|
I started experiencing this issue when trying to re scaffold my DbContext in .Net Core 2.0. This is a portion of the dump that I'm getting:
Does anyone have a fix to this? |
Everyone, sorry for not giving this more attention earlier. @berets76, thanks for the detailed investigation and repro - I can see the issue happening and I can confirm the issue is on the EF Core side. I've submitted dotnet/efcore#11846 for the EF Core to fix this. However, there may be other scenarios which also trigger this exception. To anyone getting this exception, please check if you have more than one index on the same column(s) (e.g. ascending/descending). If not, please open a new issue with a schema sample that triggers the exception. Please keep this issue about @berets76 described problem (two indices on same columns). |
If some else struggle to find duplicate indexes as I did, here is explanation how to query db https://dba.stackexchange.com/questions/204706/find-tables-with-multiple-indexes-on-same-column |
When scaffolding, we have an Npgsql-specific IndexMethod annotation to represent PostgreSQL's index methods. Previously, we would always output this annotation on indices, even when the method was the default (btree); NpgsqlAnnotationCodeGenerator would elide it as by- convention. Although this is cleaner, it caused issues whenever two indices where defined on the same column: dotnet/efcore#11846 We now scaffold the annotation only for non-default index methods as a workaround; the issue should now affect much less people. Fixes #228 (cherry picked from commit 38d2176)
Everyone, I've just pushed a commit that works around the EF Core issue (by not emitting the IndexMethod annotation unless a non-default method is actually used in the database). This is a satisfactory workaround which should address the issue for pretty much everyone (unless you really are specifying non-default index methods). The fix will be released in 2.0.3 and in the upcoming 2.1.0. |
After upgrade to 2.0.0, the scaffold-dbcontext stop work throwing this exception:
My command (working with 1.1.1 version):
EntityFrameworkCore\Scaffold-DbContext "Host=1.1.1.1;Database=myDB;Username=postgres;Password=myPWD;" -OutputDir "Models" -Force Npgsql.EntityFrameworkCore.PostgreSQL
Dependencies
Npgsql.EntityFrameworkCore.PostgreSQL 2.0.0
Npgsql.EntityFrameworkCore.PostgreSQL.Design 2.0.0-preview1
The text was updated successfully, but these errors were encountered: