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

[berets76]

After upgrade to 2.0.0, the scaffold-dbcontext stop work throwing this 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.

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

[roji]

Can you please provide a minimal SQL schema dump of your PostgreSQL which reproduces this problem?

[roji]

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.

[berets76]

Dependency removed but the exception is the same

[berets76]

Problem found.
Debugging source code, specially GetIndexes method of NpgsqlDatabaseModelFactory.cs, I found that adding indexes with a long name (in my case for example "apparecchiatureregmanutenzoni2") throw the exception.

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:
this table

CREATE TABLE public.abilitazioni
(
  abcod character(2) NOT NULL,
  abdes character varying(255),
  abnot text,
  CONSTRAINT abilitazioni_pkey PRIMARY KEY (abcod)
)

is generated as

public partial class Abilitazioni
    {
        public char Abcod { get; set; }
        public string Abdes { get; set; }
        public string Abnot { get; set; }
    }

abcod, CHARACTER(2) created as char, instead with version 1.1.1 was correctly a string.
I'm not sure issues are related, because Abilitazioni table has no indexes.

[berets76]

More details: the problem couldn't be the name length but more indexes with long names into the same table.

I changed this row in GetIndexes method

table.Indexes.Add(index);

to this one

if(index.Name.StartsWith(""))
                        table.Indexes.Add(index);

to limit indexes add.

Then I try filtering only these indexes

with this code

if(index.Name.StartsWith("apparecchiatureregmanutenzoni"))
                        table.Indexes.Add(index);

and I got the exception, but if I filter each one individually

if(index.Name.StartsWith("apparecchiatureregmanutenzoni2"))
                        table.Indexes.Add(index);

all works.

[jamiepenney]

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.

[berets76]

@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

Indexes: 4
    apparecchiatureregmanutenzoni2 IsUni:False
        Npgsql:IndexMethod - btree
    apparecchiatureregmanutenzoni3 IsUni:False
        Npgsql:IndexMethod - btree
    apparecchiatureregmanutenzoni4 IsUni:False
        Npgsql:IndexMethod - btree
    iapparecchiatureregmanutenzio1 IsUni:False
        Npgsql:IndexMethod - btree

Source table is

CREATE TABLE public.apparecchiatureregmanutenzioni
(
  mrmapp integer NOT NULL,
  mrmpro integer NOT NULL,
  mrmpia integer,
  mrmpid character varying(255),
  mrmdat date,
  mrmesi text,
  mrmprs integer,
  CONSTRAINT apparecchiatureregmanutenzioni_pkey PRIMARY KEY (mrmapp, mrmpro)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.apparecchiatureregmanutenzioni
  OWNER TO postgres;

CREATE INDEX apparecchiatureregmanutenzoni2
  ON public.apparecchiatureregmanutenzioni
  USING btree
  (mrmapp, mrmpro, mrmdat);

CREATE INDEX apparecchiatureregmanutenzoni3
  ON public.apparecchiatureregmanutenzioni
  USING btree
  (mrmdat);

CREATE INDEX apparecchiatureregmanutenzoni4
  ON public.apparecchiatureregmanutenzioni
  USING btree
  (mrmdat DESC);

CREATE INDEX iapparecchiatureregmanutenzio1
  ON public.apparecchiatureregmanutenzioni
  USING btree
  (mrmapp, mrmpia);

p.s: no comment on the benefit of some indexes

[berets76]

# 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);

[berets76]

I think the problem is in EFCore and not here.

Precisely in EFCore.Design project

        /// <summary>
        ///     Configures an index on the specified properties. If there is an existing index on the given
        ///     set of properties, then the existing index will be returned for configuration.
        /// </summary>
        /// <param name="propertyNames"> The names of the properties that make up the index. </param>
        /// <returns> An object that can be used to configure the index. </returns>
        public virtual IndexBuilder HasIndex([NotNull] params string[] propertyNames)
            => new IndexBuilder(Builder.HasIndex(Check.NotEmpty(propertyNames, nameof(propertyNames)), ConfigurationSource.Explicit));

Seems to be that EFCore check the existing of an index relying on his own properties, not considering their direction (ASC or DESC).
So, in EFCore.PG you could check "indoption" from "pg_index" table and manage it in any way

var columnOptions = reader.GetValueOrDefault<short[]>("indoption");

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).

[berets76]

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

bool IndexDuplicated(DatabaseTable table, DatabaseIndex index)
        {
            foreach (var idx in table.Indexes)
            {
                if (idx.Columns.SequenceEqual(index.Columns))
                    return true;
            }
            return false;
        }

Call and warning

void GetIndexes()
        {
            using (var command = new NpgsqlCommand(GetIndexesQuery, _connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var schemaName = reader.GetValueOrDefault<string>("nspname");
                    var tableName = reader.GetValueOrDefault<string>("cls_relname");
                    var indexName = reader.GetValueOrDefault<string>("idx_relname");

                    if (!_tableSelectionSet.Allows(schemaName, tableName))
                        continue;

                    DatabaseTable table;
                    if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
                        continue;

                    var index = new DatabaseIndex
                    {
                        Table = table,
                        Name = indexName,
                        IsUnique = reader.GetValueOrDefault<bool>("indisunique")
                    };

                    var columnIndices = reader.GetValueOrDefault<short[]>("indkey");

                    if (columnIndices.Any(i => i == 0))
                    {
                        if (reader.IsDBNull(reader.GetOrdinal("expr")))
                            throw new Exception($"Seen 0 in indkey for index {indexName} but indexprs is null");
                        index[NpgsqlAnnotationNames.IndexExpression] = reader.GetValueOrDefault<string>("expr");
                    }
                    else
                    {
                        var columns = (List<DatabaseColumn>)table.Columns;
                        for (var ordinal = 0; ordinal < columnIndices.Length; ordinal++)
                        {
                            var columnIndex = columnIndices[ordinal] - 1;
                            index.Columns.Add(columns[columnIndex]);
                        }
                    }

                    // check for indexes with same properties (duplicated)
                    if (IndexDuplicated(table, index))
                    {
                        Logger.Logger.LogWarning($"Duplicated index found on table {table.Name} : index name {index.Name}. This index has been discarded.");
                        continue;
                    }

                    index[NpgsqlAnnotationNames.IndexMethod] = reader.GetValueOrDefault<string>("amname");

                    table.Indexes.Add(index);
                }
            }
        }

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.

[adopilot]

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.

[browrp]

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:

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.MutableAnnotatableExtensions.AddAnnotations(IMutableAnnotatable annotatable, IEnumerable1 annotations) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndex(EntityTypeBuilder builder, DatabaseIndex index) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndexes(EntityTypeBuilder builder, ICollection1 indexes)
at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)
at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection1 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, IEnumerable1 tables, IEnumerable1 schemas, Boolean useDatabaseNames) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ModelScaffolder.Generate(String connectionString, IEnumerable1 tables, IEnumerable1 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, IEnumerable1 schemas, IEnumerable1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable1 schemaFilters, IEnumerable1 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_01.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.

Does anyone have a fix to this?

[roji]

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).

[adopilot]

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

[roji]

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.