efcore.pg: Cannot scaffold if two indices exist on same column

My apologies for spamming but I also have troubles trying to scrafod db. I did nu get

    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.0.0" />

And from Package Manager Console I am trying to

Scaffold-DbContext "Port=5432;Server=0.0.0.0;Database=discourse;UserId=user;Password=password;" -OutputDir "Models" -Force Npgsql.EntityFrameworkCore.PostgreSQL End than I get exception

System.InvalidOperationException: The annotation 'Npgsql:IndexMethod' cannot be added because an annotation with the same name already exists.
   at Microsoft.EntityFrameworkCore.Infrastructure.Annotatable.AddAnnotation(String name, Annotation annotation)
   at Microsoft.EntityFrameworkCore.Infrastructure.Annotatable.AddAnnotation(String name, Object value)
   at Microsoft.EntityFrameworkCore.MutableAnnotatableExtensions.AddAnnotations(IMutableAnnotatable annotatable, IEnumerable`1 annotations)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndex(EntityTypeBuilder builder, DatabaseIndex index)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndexes(EntityTypeBuilder builder, ICollection`1 indexes)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.CreateFromDatabaseModel(DatabaseModel databaseModel, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ModelScaffolder.Generate(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String projectPath, String outputPath, String rootNamespace, String contextName, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The annotation 'Npgsql:IndexMethod' cannot be added because an annotation with the same name already exists.

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.

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 https://github.com/aspnet/EntityFrameworkCore/issues/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

# Real problem The real problem is when a table has 2 different index with the same column (one ASC and one DESC); to reproduce, this table works:

CREATE TABLE public.altreproc
(
  apcod integer NOT NULL,
  aptip character(3),
  apcam character(3),
  CONSTRAINT altreproc_pkey PRIMARY KEY (apcod)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.altreproc
  OWNER TO postgres;

CREATE INDEX ialtreproc1
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default");

CREATE INDEX ialtreproc2
  ON public.altreproc
  USING btree
  (apcam COLLATE pg_catalog."default");

and this throw the exception

CREATE TABLE public.altreproc
(
  apcod integer NOT NULL,
  aptip character(3),
  apcam character(3),
  CONSTRAINT altreproc_pkey PRIMARY KEY (apcod)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.altreproc
  OWNER TO postgres;

CREATE INDEX ialtreproc1
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default");

CREATE INDEX ialtreproc2
  ON public.altreproc
  USING btree
  (apcam COLLATE pg_catalog."default");

CREATE INDEX ialtreproc3
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default" DESC);

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.