Case insensitive unique constraint for Postgres + EF Core

user_282474 0
Viewed 1368

I am using Postgres + EF Core. I have a column called Name which I want to be unique. I have tried the following:

builder.HasIndex(s => s.Name).IsUnique();

but this allows "test123" and "TEST123" to be accepted. How can I add a case insensitive unique constraint with fluent api?

Or do I just need to create a NormalizedName column and add the unique constraint to that. Seems like a lot of work every time I want to add a unique constraint to a column.

entity-framework-core asp.net-core postgresql asp.net-mvc
1 Answers

If you're using EF Core 5.0, then see below (probably the better solution). Otherwise, I added a case insensitive check by making use of the citext extension as described here. There are some limitations, I won't list them here but you can read up on them in the previous link or directly on the PostgreSQL Docs.

Also ensure you have the postgres-contrib package installed to make use of this extension.

First, enable the extension by adding it to your model builder

modelBuilder.HasPostgresExtension("citext");

Then use the citext as the column type

builder.Property(s => s.Name)
       .HasColumnType("citext")

EF Core 5.0

With EF Core 5.0 there's better support by making use of Collations. You can also check out the Npgsql docs in regards to PostrgeSQL. This gets over a bunch of limitations with citext above and gives you a lot more control.

So the (untested) steps are:

  1. Create a collation as a database object using ICU. This will create a non-deterministic, case-insensitive ICU collation. If you need something else, check out the ICU docs.
modelBuilder.HasCollation("case_insensitive_collation", locale: "en-u-ks-primary", provider: "icu", deterministic: false);
  1. Now on you column, you can add:
builder.HasIndex(s => s.Name)
       .UseCollation("case_insensitive_collation")
       .IsUnique();

If you achieved it some other way, I'd love to hear how.

user_29533350
Related