Collations and Case Sensitivity
Note
This feature is introduced in EF Core 5.0.
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. Unfortunately, full collation support is recent and somewhat incomplete, so you may need to carefully review your options below and pick the one which suits you.
PostgreSQL collations
While PostgreSQL has supported collations for a long time, supported was limited to "deterministic" collations, which did not allow for case-insensitive or accent-insensitive operations. PostgreSQL 12 introduced non-deterministic ICU collations, so it is now possible to use collations in a more flexible way. Read more about PostgreSQL collation support in the documentation.
Note
It is not yet possible to use pattern matching operators such as LIKE on columns with a non-deterministic collation.
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 some_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. While this will eventually allow specifying that all columns and operations are case-insensitive, for example, PostgreSQL currently does not support non-deterministic collations at the database level. To work around this limitation, the EF Core provider has a feature which allows you to specify the default column collation
at the model level, and the provider will ensure it is automatically specified on all columns:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasCollation("my_collation", locale: "en-u-ks-primary", provider: "icu", deterministic: false);
builder.UseDefaultColumnCollation("my_collation");
}
All columns created after this directive is added 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, the EF Core provider does the same.
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. 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:
public class Blog
{
public int Id { get; set; }
[Column(TypeName = "citext")]
public string Name { get; set; }
}
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();