A while back I tried to create a unique index on a column.
The configuration file looked something like this.

    public class DepartmentEntityConfiguration : EntityTypeConfiguration<Department>
    {
        public DepartmentEntityConfiguration()
        {
            HasKey(p => p.Id);
            Property(p => p.Alias).IsRequired().HasColumnAnnotation("Alias", new IndexAnnotation(new IndexAttribute("IX_Alias") { IsUnique = true }));
            Property(p => p.Name).IsRequired();
        }
    }

This resulted in the following migration.

            CreateTable(
                "dbo.Departments",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Alias = c.String(
                            annotations: new Dictionary<string, AnnotationValues>
                            {
                                { 
                                    "Alias",
                                    new AnnotationValues(oldValue: null, newValue: "IndexAnnotation: { Name: IX_Alias, IsUnique: True }")
                                },
                            }),
                        Name = c.String(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

Which seemed fine. It looked like it did what it supposed to do.
When the migration was run, no index or whatsoever. So I started googling about indexes and I came across the following:

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index.

So I limited the Alias to 50 characters in the configuration file:

    public class DepartmentEntityConfiguration : EntityTypeConfiguration<Department>
    {
        public DepartmentEntityConfiguration()
        {
            HasKey(p => p.Id);
            Property(p => p.Alias).IsRequired().HasMaxLength(50).HasColumnAnnotation("Alias", new IndexAnnotation(new IndexAttribute("IX_Alias") { IsUnique = true }));
            Property(p => p.Name).IsRequired();
        }
    }

But still no Index. So I continued my search on the internet and I Finally found the problem.
It is the name of the HasColumnAnnotion method. This should be set to “Index” when you want to create an Index. This seems a bit unnecessary to me when the second argument is an IndexAnnotation. So once again I changed my configuration file:

    public class DepartmentEntityConfiguration : EntityTypeConfiguration<Department>
    {
        public DepartmentEntityConfiguration()
        {
            HasKey(p => p.Id);
            Property(p => p.Alias).IsRequired().HasMaxLength(50).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Alias") { IsUnique = true }));
            Property(p => p.Name).IsRequired();
        }
    }

The migration file generated:

            CreateIndex("dbo.Departments", "Alias", unique: true);

So now I know that the HasColumnAnnotation name is fixed on “Index”, I would recommend creating an extension method for creating unique indexes:

    public static class PrimitivePropertyConfigurationExtensions
    {
        public static PrimitivePropertyConfiguration IsUnique(this PrimitivePropertyConfiguration configuration)
        {
            return configuration.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute { IsUnique = true }));
        }
    }

And you can use it as follows:

    public class DepartmentEntityConfiguration : EntityTypeConfiguration<DepartmentState>
    {
        public DepartmentEntityConfiguration()
        {
            HasKey(p => p.Id);
            Property(p => p.Alias).IsRequired().HasMaxLength(50).IsUnique();
            Property(p => p.Name).IsRequired();
        }
    }
The following two tabs change content below.
I'm a software developer from Utrecht. Interested in DDD, continuous delivery, new technologies & frameworks.

Latest posts by Vincent Keizer (see all)