Add a unique nullable index to an entity property
Tags: csharp, entityframework, dotnetcoreidentity, sqlserver
Published: 2018-08-29 14:11:36

---
title: Add a unique nullable index to an entity property
published: true
description: 
tags: csharp, entityframework, dotnetcoreidentity, sqlserver
---

## 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:

```csharp
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:

```csharp
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`.

```csharp
// 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:

```csharp
// 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:

```csharp
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:

```csharp
// 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.

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

That's it!