loading...

Add a unique nullable index to an entity property

alexruzenhack profile image Alex Ruzenhack Updated on ・2 min read

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!

Discussion

pic
Editor guide
Collapse
r0bnet profile image
rob

Sometimes i ask myself if some articles here are really worth the work. No offense but i think what you mention above should more or less be common sense if you know the basics of nullable types in C#.

Collapse
alexruzenhack profile image
Alex Ruzenhack Author

Let's see what Barbara Oakley say about learning in 10 Top Ideas to Help Your Learning. Here I think I'm predominantly doing well 2 steps:

  • 2) Create brain‐links with practice;
  • 6) Test yourself. Have others test you. Teach others.

Now, you can help me to achieve a better value in my posting. What do you want to see here?

Collapse
andy profile image
Andy Zhao (he/him)

Learning by teaching, presenting, and getting feedback has been proven effective for a long time now, and that in and of itself is worth the work. Everyone has different learning styles, and writing (to share with others or not) is one way.

Collapse
alexruzenhack profile image
Alex Ruzenhack Author

I'm not offended. I agree with you it is the basic. But I'm learning and using this blog to teach me and make clear some basic things across a solution. Like me, should exist someone needing to do stuff even without knowing the basics. Sometimes you are pressed to skip the basic and easily can found lost.