Add a unique nullable index to an entity property

The problem

I was working on an MVC project and to simplify our life, the team chose to work with the ASP.NET Core Identity Assembly to create the authentication and authorization logic of the web app.

However, when a user registers yourself on the app, a new Id is generated for them, despite the company may already have one Id.

Then, to link together the new Id with the natural old Id, I extended the default IdentityUser to possess the MyCompanyId property, this way:

public class CustomUser : IdentityUser
{
    public int MyCompanyId { get; set; }
}

And in order to make this change effective I extended the IdentityDbContext to use my CustomUser and also to register the property MyCompanyId as a unique index, like this:

public class CustomDbContext : IdentityDbContext<CustomUser>
{
    public CustomDbContext(DbContextOptions<CustomDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<CustomUser>(user => 
        {
            user.HasIndex(x => x.MyCompanyId).IsUnique(true);
        });
    }
}

With this, when a user registers yourself a default value is assigned to MyCompanyId property, which is 0. When another user tries to register, the same value will be assigned to their MyCompanyId, and it will throw an error of duplicate key.

It occurs because when the entity is transcribed to a fluentish API for migration, by default, the column is set as non-nullable and a default value must be assigned, as we saw 0.

// hidden for brevity
...
migrationBuilder.CreateTable(
    name: "AspNetUsers",
    columns: table => new
    {
        Id = table.Column<string>(nullable: false),
        ...
        MyCompanyId = table.Column<int>(nullable: false)
    },
...

And the index assigns uniqueness to all values:

// hidden for brevity
...
migrationBuilder.CreateIndex(
    name: "IX_AspNetUsers_MyCompanyId",
    table: "AspNetUsers",
    column: "MyCompanyId",
    unique: true);
...

The solution

How to solve this all, and allow a nullable unique index? It's so simple, man! Just add the ? symbol after the type of the property, this way:

public class CustomUser : IdentityUser
{
    public int? MyCompanyId { get; set; }
}

Now the property is optional and it will be propagated all way down. Let's see the migration fluentish API:

// hidden for brevity
...
migrationBuilder.CreateTable(
    name: "AspNetUsers",
    columns: table => new
    {
        Id = table.Column<string>(nullable: false),
        ...
        MyCompanyId = table.Column<int>(nullable: true) // now it's true
    },
...

And the index has a new variable filter which skip the null values as unique. It means you can have as many null values as you want.

// hidden for brevity
...
migrationBuilder.CreateIndex(
    name: "IX_AspNetUsers_MyCompanyId",
    table: "AspNetUsers",
    column: "MyCompanyId",
    unique: true,
    filter: "[MyCompanyId] IS NOT NULL");
...

That's it!