Releases: VirtoCommerce/vc-platform
3.1007.0
3.917.3
3.895.2
3.917.2
3.887.9
3.1006.0
3.1005.0
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 AddCaseInsensitiveCollations5. 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.