The annotation 'SqlServer:Clustered' cannot be added because an annotation with the same name already exists.

[sam-wheat]

Hello, I am receiving the error message below when I try to scaffold a database. What does this mean and how can I work around it?

Thank you

System.InvalidOperationException: The annotation 'SqlServer:Clustered' cannot be
 added because an annotation with the same name already exists.
   at Microsoft.EntityFrameworkCore.Infrastructure.Annotatable.AddAnnotation(Str
ing name, Annotation annotation)
   at Microsoft.EntityFrameworkCore.MutableAnnotatableExtensions.AddAnnotations(
IMutableAnnotatable annotatable, IEnumerable`1 annotations)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingMo
delFactory.VisitIndex(EntityTypeBuilder builder, DatabaseIndex index)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingMo
delFactory.VisitIndexes(EntityTypeBuilder builder, ICollection`1 indexes)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingMo
delFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingMo
delFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingMo
delFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseM
odel)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingMo
delFactory.Create(DatabaseModel databaseModel, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffold
er.ScaffoldModel(String connectionString, IEnumerable`1 tables, IEnumerable`1 sc
hemas, String namespace, String language, String contextDir, String contextName,
 ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOption
s)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldC
ontext(String provider, String connectionString, String outputDir, String output
ContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tabl
es, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames
)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl
(String provider, String connectionString, String outputDir, String outputDbCont
extDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 ta
bleFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatab
aseNames)
   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.Execu
te(Action action)
The annotation 'SqlServer:Clustered' cannot be added because an annotation with
the same name already exists.

Command:

dotnet ef dbcontext scaffold --startup-project ..\NPBOverrides.DbContextServices "server=zzz" 
Microsoft.EntityFrameworkCore.SqlServer 

EF Version: 2.1 dotnet: 2.1.3000 OS: Windows 7

[smitpatel]

We will need the table DDL in database to investigate this issue. Can you try using verbose flag to try locate the table causing the error and post its definition?

[sam-wheat]

used --verbose but got no additonal info in the error message itself. The table alphabetically following the last one in the output before the error is this:

/****** Object:  Table [APP].[BloombergActiveTickers]    Script Date: 5/31/2018 2:33:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [APP].[BloombergActiveTickers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Ticker] [varchar](50) NOT NULL,
    [MarketCap] [float] NULL,
    [ShortTermDebt] [float] NULL,
    [LongTermDebt] [float] NULL,
    [UpdatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BloombergActiveTickers] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [APP].[BloombergActiveTickers] ADD  CONSTRAINT [df_bloomberg_active_ticker_updateddate]  DEFAULT (getdate()) FOR [UpdatedDate]
GO

[smitpatel]

That table works fine. Look for table which has clustered index defined on it.

[sam-wheat]

I scripted the entire DB. These are the tables with clustered indexes:

CREATE TABLE [FIN].[ESGRiskMetrics](
    [ESGRiskMetricsSK] [bigint] IDENTITY(1,1) NOT NULL,
) ON [PRIMARY]

GO
/****** Object:  Index [IDX_ESGRiskMetrics_SK]    Script Date: 5/31/2018 3:21:44 PM ******/
CREATE UNIQUE CLUSTERED INDEX [IDX_ESGRiskMetrics_SK] ON [FIN].[ESGRiskMetrics]
(
    [ESGRiskMetricsSK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [APP].[ClassDescriptionMap](
    [ClassID] [nvarchar](255) NULL,
    [ClassDescription] [nvarchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IDX_ClassDescriptionMap_ClassID]    Script Date: 5/31/2018 3:21:44 PM ******/
CREATE UNIQUE CLUSTERED INDEX [IDX_ClassDescriptionMap_ClassID] ON [APP].[ClassDescriptionMap]
(
    [ClassID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [STATIC].[AttributionPeriods](
    [EndDate] [date] NOT NULL,
    [Period] [varchar](50) NOT NULL,
    [StartDate] [date] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [PK_AttributionPeriods]    Script Date: 5/31/2018 3:21:44 PM ******/
CREATE CLUSTERED INDEX [PK_AttributionPeriods] ON [STATIC].[AttributionPeriods]
(
    [EndDate] ASC,
    [Period] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

[smitpatel]

None of them defines PK so they are not scaffolded. I believe we would need whole database design dump or if you can use --schema/table args to isolate the table causing the issue.

[sam-wheat]

I was able to create tables for several schemas. I narrowed it down to one schema and within that, this table:

/****** Object:  Table [FIN].[Analytics]    Script Date: 5/31/2018 3:54:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [FIN].[Analytics](
    [AnalyticsSK] [int] IDENTITY(1,1) NOT NULL,

 CONSTRAINT [PK_Analytics] PRIMARY KEY NONCLUSTERED 
(
    [AnalyticsSK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

Exact command used:

dotnet ef dbcontext scaffold --startup-project ..\NPBOverrides.DbContextServices "server=zzzz" Microsoft.EntityFrameworkCore.SqlServer --force --table Analytics

Command crashes every time. This table has no FK's defined.

[smitpatel]

That worked for me. Is there any other table named Analytics in other schemas? (above command would scaffold all of them). Can you also tell exact version of EF Core?

[sam-wheat]

No other Analytics tables Edit: There is Analytics2 which does NOT crash

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [FIN].[Analytics2](
    [AnalyticsSK] [bigint] NOT NULL,

 CONSTRAINT [PK_Analytics2] PRIMARY KEY NONCLUSTERED 
(
    [AnalyticsSK] ASC,
    [AnalyticsEffectiveDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

EF versions:

<PackageReference Include="Inflector.NetStandard" Version="1.2.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.0" />

I've tried disabling Pluralizer it has no effect:

public class DbContextDesignerBase : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<IPluralizer, CustomPluralizer>();
    }
}

[sam-wheat]

FIN.Analytics crashes. This is a very large table I have removed the columns for confidentiality. Note EntityID column - could that be an issue? FIN.Analytics2 has no EntityID column.

CREATE TABLE [FIN].[Analytics]( [AnalyticsSK] [int] IDENTITY(1,1) NOT NULL, [EntityID] varchar NULL, [Source] varchar NULL, CONSTRAINT [PK_Analytics] PRIMARY KEY NONCLUSTERED ( [AnalyticsSK] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

If you want full schema please send an email to sam dot wheat at outlook dot com.

[sam-wheat]

Has there been any movement on this? Can I provide any more info? Please see my comment above regarding email. This is a blocker for me, any suggestions how to work around this or further troubleshoot are greatly appreciated.

[smitpatel]

@sam-wheat - Sent you mail.

[ajcvickers]

Duplicate of #11846