DEV Community

Alex Ruzenhack
Alex Ruzenhack

Posted on • Edited on

5 1

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; }
}
Enter fullscreen mode Exit fullscreen mode

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);
        });
    }
}
Enter fullscreen mode Exit fullscreen mode

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)
    },
...
Enter fullscreen mode Exit fullscreen mode

And the index assigns uniqueness to all values:

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

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; }
}
Enter fullscreen mode Exit fullscreen mode

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
    },
...
Enter fullscreen mode Exit fullscreen mode

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");
...
Enter fullscreen mode Exit fullscreen mode

That's it!

Heroku

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site

Top comments (4)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay