Efcore: scaffold index from existing database leads to abiguous reference

[digitalsigi]

scaffolding existing DB Table with index leads to

Error CS0104 'Index' is an ambiguous reference between 'Microsoft.EntityFrameworkCore.IndexAttribute' and 'System.ComponentModel.DataAnnotations.Schema.IndexAttribute' AVS_Schema C:\GITatUHU\rvsweb\src\AVS_Schema\Models\AVS_DB\Zeytungsdatum.cs

Quick fix was
using IndexAttribute = Microsoft.EntityFrameworkCore.IndexAttribute;

This Table definition:

CREATE TABLE [dbo].[Zeytungsdatum] (
    [ZeytungId]  INT           IDENTITY (1, 1) NOT NULL,
    [ZeytungNr]  NVARCHAR (10) NOT NULL,
    [RedSchluss] DATE          DEFAULT ('18591010') NOT NULL,
    [ErschDat]   DATE          DEFAULT ('18591010') NOT NULL,
    [BearbDatum] DATETIME      DEFAULT (getdate()) NOT NULL,
    [Bearbeiter] NVARCHAR (50) NOT NULL,
    [RV]         ROWVERSION    NOT NULL,
    CONSTRAINT [PK_Zeytungsdatum] PRIMARY KEY CLUSTERED ([ZeytungId] ASC),
    CONSTRAINT [AK_Zeytungsdatum_ZeytungNr] UNIQUE NONCLUSTERED ([ZeytungNr] ASC),
    CONSTRAINT [CK_Zeytungsdatum_RedSchluss] CHECK ([RedSchluss]>='18591010'),
    CONSTRAINT [CK_Zeytungsdatum_ErschDat] CHECK ([ErschDat]>=[RedSchluss])
);

produces with
dotnet ef scaffold .... --data-annotations
this C# class:

```C#
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

nullable disable

namespace AVS_Schema.Models.AVS_DB
{
[Table("Zeytungsdatum")]
[Index(nameof(ZeytungNr), Name = "AK_Zeytungsdatum_ZeytungNr", IsUnique = true)]
public partial class Zeytungsdatum
{
public Zeytungsdatum()
{
Werdegangs = new HashSet();
}

    [Key]
    public int ZeytungId { get; set; }
    [Required]
    [StringLength(10)]
    public string ZeytungNr { get; set; }
    [Column(TypeName = "date")]
    public DateTime RedSchluss { get; set; }
    [Column(TypeName = "date")]
    public DateTime ErschDat { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime BearbDatum { get; set; }
    [Required]
    [StringLength(50)]
    public string Bearbeiter { get; set; }
    [Required]
    public byte[] RV { get; set; }

    public virtual ICollection<Werdegang> Werdegangs { get; set; }
}

}

### Verbose output

dotnet ef dbcontext scaffold "Server=mistauch;Database=AVS_ADB_20201126;Integrated Security=False;Persist Security Info=False; User Id=uhuweb;MultipleActiveResultSets=true;;Password=**" Microsoft.EntityFrameworkCore.SqlServer -v --data-annotations -c AVS_DBContext --context-dir AVS_DB --output-dir Models\AVS_DB --use-database-names --force --no-build -t Zeytungsdatum"
Using project 'C:\GITatUHU\rvsweb\src\AVS_Schema\AVS_Schema.csproj'.
Using startup project 'C:\GITatUHU\rvsweb\src\AVS_Schema\AVS_Schema.csproj'.
Writing 'C:\GITatUHU\rvsweb\src\AVS_Schema\obj\AVS_Schema.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\jbogad\AppData\Local\Temp\tmp8B8A.tmp /verbosity:quiet /nologo C:\GITatUHU\rvsweb\src\AVS_Schema\AVS_Schema.csproj
Writing 'C:\GITatUHU\rvsweb\src\AVS_Schema\obj\AVS_Schema.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\jbogad\AppData\Local\Temp\tmp932C.tmp /verbosity:quiet /nologo C:\GITatUHU\rvsweb\src\AVS_Schema\AVS_Schema.csproj
dotnet exec --depsfile C:\GITatUHU\rvsweb\src\AVS_Schema\bin\Debug\net5.0\AVS_Schema.deps.json --additionalprobingpath C:\Users\jbogad.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig C:\GITatUHU\rvsweb\src\AVS_Schema\bin\Debug\net5.0\AVS_Schema.runtimeconfig.json C:\Users\jbogad.dotnet\tools.store\dotnet-ef\5.0.0\dotnet-ef\5.0.0\tools\netcoreapp3.1\any\tools\netcoreapp2.0\any\ef.dll dbcontext scaffold "Server=mistauch;Database=AVS_ADB_20201126;Integrated Security=False;Persist Security Info=False; User Id=uhuweb;MultipleActiveResultSets=true;;Password=uhuweb123+#" Microsoft.EntityFrameworkCore.SqlServer --data-annotations -c AVS_DBContext --context-dir AVS_DB --output-dir Models\AVS_DB --use-database-names --force -t Zeytungsdatum --assembly C:\GITatUHU\rvsweb\src\AVS_Schema\bin\Debug\net5.0\AVS_Schema.dll --startup-assembly C:\GITatUHU\rvsweb\src\AVS_Schema\bin\Debug\net5.0\AVS_Schema.dll --project-dir C:\GITatUHU\rvsweb\src\AVS_Schema\ --language C# --working-dir C:\GITatUHU\rvsweb\src\AVS_Schema --verbose --root-namespace AVS_Schema
Using assembly 'AVS_Schema'.
Using startup assembly 'AVS_Schema'.
Using application base 'C:\GITatUHU\rvsweb\src\AVS_Schema\bin\Debug\net5.0'.
Using working directory 'C:\GITatUHU\rvsweb\src\AVS_Schema'.
Using root namespace 'AVS_Schema'.
Using project directory 'C:\GITatUHU\rvsweb\src\AVS_Schema\'.
Remaining arguments: .
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding design-time services referenced by assembly 'AVS_Schema'...
Finding design-time services referenced by assembly 'AVS_Schema'...
No referenced design-time services were found.
Finding IDesignTimeServices implementations in assembly 'AVS_Schema'...
No design-time services were found.
To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
Found default schema 'dbo'.
Found type alias with name: sys.sysname which maps to underlying data type nvarchar(128).
Found table with name: dbo.Zeytungsdatum.
Found column with table: dbo.Zeytungsdatum, column name: ZeytungId, ordinal: 1, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: True, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.Zeytungsdatum, column name: ZeytungNr, ordinal: 2, data type: sys.nvarchar, maximum length: 20, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.Zeytungsdatum, column name: RedSchluss, ordinal: 3, data type: sys.date, maximum length: 3, precision: 10, scale: 0, nullable: False, identity: False, default value: ('18591010'), computed value: (null), computed value is stored: False
Found column with table: dbo.Zeytungsdatum, column name: ErschDat, ordinal: 4, data type: sys.date, maximum length: 3, precision: 10, scale: 0, nullable: False, identity: False, default value: ('18591010'), computed value: (null), computed value is stored: False
Found column with table: dbo.Zeytungsdatum, column name: BearbDatum, ordinal: 5, data type: sys.datetime, maximum length: 8, precision: 23, scale: 3, nullable:
False, identity: False, default value: (getdate()), computed value: (null), computed value is stored: False
Found column with table: dbo.Zeytungsdatum, column name: Bearbeiter, ordinal: 6, data type: sys.nvarchar, maximum length: 100, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.Zeytungsdatum, column name: RV, ordinal: 7, data type: sys.timestamp, maximum length: 8, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found primary key with name: PK_Zeytungsdatum, table: dbo.Zeytungsdatum.
Found unique constraint with name: AK_Zeytungsdatum_ZeytungNr, table: dbo.Zeytungsdatum.
```

Provider and version information

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system:
IDE: Visual Studio 2019 16.8.2

[ajcvickers]

Note for triage: this will happen if the project is referencing EF6 and we add a using for System.ComponentModel.DataAnnotations.Schema since that's the namespace for IndexAttribuite in the EF6 assembly.

[ajcvickers]

If you need to reference EntityFramework.dll from the project, then the workaround is to update the generated code to explicitly reference the EFCore IndexAttribute using its namespace. We could consider trying to do this automatically if EF6 is referenced, but such a change is not trivial, so we'll wait for further feedback before deciding whether to fix this.

[digitalsigi]

Note for triage: this will happen if the project is referencing EF6 and we add a using for System.ComponentModel.DataAnnotations.Schema since that's the namespace for IndexAttribuite in the EF6 assembly.

The point is - from my perspective -

1. dotnet ef scaffold produces non compiling code - that should not happen
2. There is a small but important difference between System.ComponentModel.DataAnnotations.Schema and Microsoft.EntityFrameworkCore.IndexAttribute: The first seems not to accept compound keys, the latter does.

The project does not explicitly reference System.ComponentModel.DataAnnotations.Schema but Microsoft.EntityFrameworkCore. All of Version 5.0.0.

[digitalsigi]

If you need to reference EntityFramework.dll from the project, then the workaround is to update the generated code to explicitly reference the EFCore IndexAttribute using its namespace. We could consider trying to do this automatically if EF6 is referenced, but such a change is not trivial, so we'll wait for further feedback before deciding whether to fix this.

This is what I have done. However, dotnet ef scaffold should generate code, which compiles without errors.

[ajcvickers]

@digitalsigi

dotnet ef scaffold produces non compiling code - that should not happen

This happens because you are referencing both EF6 and EF Core in the same project. This is generally not a great idea because it can often cause confusion between types--for example, both also contain a DbContext class.

There is a small but important difference between System.ComponentModel.DataAnnotations.Schema and Microsoft.EntityFrameworkCore.IndexAttribute: The first seems not to accept compound keys, the latter does.

There is a much more important difference between them: the former works with EF6 and is ignored by EF Core, while the latter works with EF Core and is ignored by EF6.

The project does not explicitly reference System.ComponentModel.DataAnnotations.Schema

This is a namespace, so not really something that can be referenced. Types in this namespace can come from multiple assemblies; in this case the type is coming from EntityFramework.dll, which is the EF6 assembly.

[digitalsigi]

@ajcvickers My point is, the code aboe is generated by dotnet ef scaffold. It has not been modified by me. So its not me referencing both EF6 and EF Core in the same project, why does dotnet ef scaffolding generate this code?

Furthermore: I asked dotnet ef scaffold --data-annotations. When removing EF6 using then all the annotations get flagged, removing EF Core flags the Index and Table attributes.

Again:
dotnet ef scaffold should generate correct code.

  <ItemGroup>
    <PackageReference Include="ChoEtl" Version="1.2.1.3" />
    <PackageReference Include="ChoETL.JSON" Version="1.2.1.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SQlServer" Version="5.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.0">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Newtonsoft.Json" Version="12.0.3" />
    <PackageReference Include="Newtonsoft.Json.Schema" Version="3.0.13" />
    <PackageReference Include="NJsonSchema" Version="10.3.1" />
    <PackageReference Include="NJsonSchema.CodeGeneration" Version="10.3.1" />
    <PackageReference Include="NJsonSchema.CodeGeneration.CSharp" Version="10.3.1" />
    <PackageReference Include="System.CommandLine" Version="2.0.0-beta1.20253.1" />
    <ProjectCapability Include="DynamicDependentFile" />
    <ProjectCapability Include="DynamicFileNesting" />
  </ItemGroup>

As you can see EFCore is referencing EF6.

[smitpatel]

When removing EF6 using then all the annotations get flagged

This is incorrect. They should work correctly.

[ErikEJ]

Maybe a repro project would be useful?

[digitalsigi]

@ErikEJ I'll see whether I can find the time to create a sample which reproduces behaviour.