3.1004.0

🎯 Development

• Case-Insensitive Search for PostgreSQL 18 (#2979)

🔥 Breaking Change:

PostgreSQL 18 is now the minimum required version. If you are currently using PostgreSQL v16 or v17, you must either:

• Upgrade to PostgreSQL 18, or
• Remove the case_insensitive collation manually from your database schema

👨‍💻 Adding Case-Insensitive Search Support for PostgreSQL into Virto Commerce Module

By default, PostgreSQL performs case-sensitive string comparisons, unlike SQL Server. This guide walks you through enabling case-insensitive search for specific entity properties.

1. Update Platform Dependency

Update your module's platform dependency to version 3.1004 or later to get access to the required PostgreSQL extension methods.

2. Identify Properties for Case-Insensitive Search

Determine which entity properties should support case-insensitive search. Apply this only to properties that require it — typically user-facing fields used in search or filtering.

Note: Avoid applying case-insensitive collation to all string properties indiscriminately, as it may affect performance and index behavior.

3. Add Entity Type Configurations

Create an IEntityTypeConfiguration class in your MyCompany.MyModuleName.Data.PostgreSql project for each entity that needs case-insensitive properties.

Use the UseCaseInsensitiveCollation() extension method from VirtoCommerce.Platform.Data.PostgreSql.Extensions.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using VirtoCommerce.Platform.Data.Model;
using VirtoCommerce.Platform.Data.PostgreSql.Extensions;

public class MyClassEntityConfiguration : IEntityTypeConfiguration<MyClass>
{
    public void Configure(EntityTypeBuilder<MyClass> builder)
    {
        builder.Property(x => x.Name).UseCaseInsensitiveCollation();
        builder.Property(x => x.UserName).UseCaseInsensitiveCollation();
        builder.Property(x => x.Email).UseCaseInsensitiveCollation();       
    }
}

4. Create an EF Core Migration

Generate a new migration from the PostgreSQL data project directory:

Run

dotnet ef migrations add AddCaseInsensitiveCollations

5. Review the Generated Migration

Open the generated migration file and verify that it contains AlterColumn calls specifying the case-insensitive collation for the expected columns.

6. Add the Collation Creation Extension

At the beginning of the Up method in the generated migration, add the CreateCaseInsensitiveCollationIfNotExists() call from VirtoCommerce.Platform.Data.PostgreSql.Extensions. This ensures the required collation exists in the database before any columns reference it:

using Microsoft.EntityFrameworkCore.Migrations;
using VirtoCommerce.Platform.Data.PostgreSql.Extensions

...

    public partial class AddCaseInsensitiveCollations : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateCaseInsensitiveCollationIfNotExists();
            ...
            

7. Install and Test

Build and install the module. Verify that EF Core Contains() and equality (==) operations are now case-insensitive for the configured properties.