# Collations and Case Sensitivity

> It's recommended that you start by reading the general Entity Framework Core docs on collations and case sensitivity.

PostgreSQL is a case-sensitive database by default, but provides various possibilities for performing case-insensitive operations and working with collations. Depending on your PostgreSQL version, full collation support may be somewhat incomplete, so you may need to carefully review your options below and pick the one which suits you.

## PostgreSQL collations

PostgreSQL has full support for managing collations, and this is the recommended way for managing textual comparisons and sorting. However, note that ; support for non-deterministic collations - coverting case-insensitivity - was relatively limited in some ways. While recent versions such as PostgreSQL 18 support case-insensitive collations quite well, previous versions did not (for example, it was not possible perform pattern matching via LIKE on columns with a non-deterministic collation). Read more about PostgreSQL collation support in the documentation.

### Creating a collation

In PostgreSQL, collations are first-class, named database objects which can be created and dropped, just like tables. To create a collation, place the following in your context's `OnModelCreating`:

    modelBuilder.HasCollation("my_collation", locale: "en-u-ks-primary", provider: "icu", deterministic: false);

This creates a collation with the name `my_collation`: this is an arbitrary name you can choose, which you will be specifying later when assigning the collation to columns. The rest of the parameters instruct PostgreSQL to create a non-deterministic, case-insensitive ICU collation. ICU collations are very powerful, and allow you to specify precise rules with regards to case, accents and other textual aspects. Consult the ICU docs for more information on supported features and keywords.

### Column collation

Once a collation has been created in your database, you can specify it on columns:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasCollation("my_collation", locale: "en-u-ks-primary", provider: "icu", deterministic: false);

        modelBuilder.Entity<Customer>().Property(c => c.Name)
            .UseCollation("my_collation");
    }

This will cause all textual operators on this column to be case-insensitive.

### Database collation

PostgreSQL also allows you to specify collations at the database level, when it is created:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.UseCollation("<collation_name>");
    }

Unfortunately, the database collation is quite limited in PostgreSQL; it notably does not support non-deterministic collations (e.g. case-insensitive ones). To work around this limitation, you can use EF Core's pre-convention model configuration feature:

    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Properties<string>().UseCollation("my_collation");
    }

All columns created with this configuration will automatically have their collation specified accordingly, and all existing columns will be altered. The end result of the above is very similar to specifying a database collation: instead of telling PostgreSQL to implicit apply a collation to all columns, EF Core will do the same for you in its migrations.

## The citext type

The older PostgreSQL method for performing case-insensitive text operations is the `citext` type; it is similar to the `text` type, but operators are functions between `citext` values are implicitly case-insensitive. Use this type only if case-insensitive collations (see above) are insufficient for your purposes. The PostgreSQL docs provide more information on this type.

`citext` is available in a PostgreSQL-bundled extension, so you'll first have to install it:

    modelBuilder.HasPostgresExtension("citext");

Specifying that a column should use `citext` is simply a matter of setting the column's type:

- Data Annotations
- Fluent API

    public class Blog
    {
        public int Id { get; set; }
        [Column(TypeName = "citext")]
        public string Name { get; set; }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().Property(b => b.Name)
            .HasColumnType("citext");
    }

Some limitations (others are listed in the PostgreSQL docs):

- While `citext` allows case-insensitive comparisons, it doesn't handle other aspects of collations, such as accents.
- Several PostgreSQL text functions are overloaded to work with `citext` as expected, but others aren't. Using a function that isn't overloaded will result in a regular, case-sensitive match.
- Unlike collations, `citext` does not allow the same column to be compared case-sensitively in some queries, and and insensitively in others.

## ILIKE

`ILIKE` is a PostgreSQL-specific operator that works just like `LIKE`, but is case-insensitive. If you only need to perform case-insensitive `LIKE` pattern matching, then this could be sufficient. The provider exposes this via `EF.Functions.ILike`:

    var results = ctx.Blogs
        .Where(b => EF.Functions.ILike(b.Name, "a%b"))
        .ToList();