Fluent Api is NOT creating a fully correct Migration file content, so that unique indexes are not being applied to the database

Question

(Title has been altered, see edits below for changes)

Current project:

DotNet 4.7
MVC 5
C# 7.1
MSSQL Server 2012 (latest, all up to date)

When I use the fluent API to define a table with two unique indexes:

Property(x => x.UserId)
  .HasColumnOrder(1)
  .HasColumnName("UserId")
  .HasColumnType("uniqueidentifier")
  .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
  .HasColumnAnnotation(
    "UserId", 
    new IndexAnnotation(
      new IndexAttribute("IX_UserId") { 
        IsUnique = true 
      }
    )
  )
  .IsRequired();
Property(x => x.UserName)
  .HasColumnOrder(2)
  .HasColumnName("UserName")
  .HasColumnType("nvarchar")
  .HasMaxLength(256)
  .HasColumnAnnotation(
    "Username", 
    new IndexAnnotation(
      new IndexAttribute("IX_Username") { 
        IsUnique = true 
      }
    )
  )
  .IsRequired();

I am seeing the correct Migration entries in the migrations file:

UserId = c.Guid(
  nullable: false, 
  identity: true, 
  defaultValueSql: "NEWID()",
  annotations: new Dictionary<string, AnnotationValues> {
    { 
      "UserId", 
      new AnnotationValues(
        oldValue: null, 
        newValue: "IndexAnnotation: { 
          Name: IX_UserId, 
          IsUnique: True 
        }"
      ) 
    } 
  }
),
UserName = c.String(
  nullable: false, 
  maxLength: 256, 
  annotations: new Dictionary<string, AnnotationValues> {
    { 
      "Username", 
      new AnnotationValues(
        oldValue: null, 
        newValue: "IndexAnnotation: { 
          Name: IX_Username, 
          IsUnique: True 
        }"
      ) 
    } 
  }
),

However when I examine the database, and look at the keys/indexes for the table, I am noticing that the UserId index is not named IX_UserId (it is called a rather default PK_dbo.User), and that no index/uniqueness exists for the UserName at all. I need to create it manually in SQL Management Studio.

As in, while EF Fluent API was able to create what looks like the correct Migration file, complete with all proper attributes, this did not translate into a fully successful migration to the DB, as only required, default indexes were created (all index info specified in the migration file were totally ignored in favour of default decoration of primary keys).

Can anyone explain where I went wrong?

Edit:

Hmmmm… just ran across this other example, perhaps it is where I went wrong? According to it, my Fluent API should be like this:

Property(x => x.UserId)
  .HasColumnOrder(1)
  .HasColumnName("UserId")
  .HasColumnType("uniqueidentifier")
  .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
  .HasColumnAnnotation(
    "UserId", 
    new IndexAnnotation(
      new IndexAttribute("IX_UserId") { 
        IsUnique = true,
        Order = 1
      }
    )
  )
  .IsRequired();
Property(x => x.UserName)
  .HasColumnOrder(2)
  .HasColumnName("UserName")
  .HasColumnType("nvarchar")
  .HasMaxLength(256)
  .HasColumnAnnotation(
    "Username", 
    new IndexAnnotation(
      new IndexAttribute(),
      new IndexAttribute("IX_Username") { 
        IsUnique = true 
        Order = 2
      }
    )
  )
  .IsRequired();

Am I reading that other post correctly?

Edit 2:

Uhhhh… I just discovered that the correct index names are being used, but only on where those keys exist as foreign keys in other tables. So wherever the UserId exists in other tables as a foreign key, it has the correct index name of IX_UserId even though my Fluent API never described an index for that foreign key in that table.

Whisky. Tango. Foxtrot.

Edit 3:

Fluent API is NOT creating the correct migration file. Just compared the foreign keys of those other tables to the migration file, and those other tables clearly have the following content:

.Index(t => t.UserId)
.Index(t => t.CourseId);

So yeah, Fluent API is not building the correct migration file, in that it is missing a whole bunch of those .Index() entries for primary keys and the like.

Karel Tamayo Karel Tamayo · Accepted Answer · 2017-10-31T16:43:58

Looking at the docs for the HasColumnAnnotation method it says:

My guess will be that you're not using the valid C#/EDM annotation name. Try using IndexAnnotation.AnnotationName constant as the parameter to your index:

using System.Data.Entity.Infrastructure.Annotations;

...

Property(x => x.UserId)
    ...
    .HasColumnAnnotation(
         IndexAnnotation.AnnotationName, <-- this is the valid annotation name. 
          new ndexAttribute("Ix_UserId") {IsUnique = true});

That's a constant value equals to "Index" but I'm suspecting this could be the cause of your issues.

Hope this helps!

Fluent Api is NOT creating a fully correct Migration file content, so that unique indexes are not being applied to the database

1 Answers