Case-Insensitive Sort in .NET-PostgreSQL Application

Introduction

When migrating a .NET application from Microsoft SQL Server (MSSQL) to PostgreSQL, one of the most common surprises is a change in string sorting behavior. MSSQL typically uses case-insensitive collations by default, while PostgreSQL treats text comparisons as case-sensitive unless configured otherwise.

This difference can cause unexpected ordering results, failing tests, and inconsistent user-facing sorting after migration.

This article explains why the behavior differs and presents two practical solutions: handling case-insensitive sorting in .NET using StringComparer.OrdinalIgnoreCase, or configuring PostgreSQL to behave similarly using the citext extension.

The Problem: Different Default Collations

Consider the following table:

order	name
1	b
2	B
3	a
4	a1
5	a11
6	a2
7	a20
8	A
9	a19

Running the query below in PostgreSQL:

1
SELECT name FROM testsorting ORDER BY name;

PostgreSQL Result

1
A
2
B
3
a
4
a1
5
a11
6
a19
7
a2
8
a20
9
b

At first glance, this may look incorrect. Many developers expect a case-insensitive order such as a, A, b, B. However, PostgreSQL sorts text based on binary comparison by default, where uppercase letters come before lowercase letters.

Why PostgreSQL Sorts Uppercase Before Lowercase

PostgreSQL follows standard ASCII/Unicode ordering, where:

1
'A' (65) < 'a' (97)

Because of this, all uppercase letters are sorted before lowercase letters unless a case-insensitive collation or data type is used.

In contrast, MSSQL commonly uses collations such as SQL_Latin1_General_CP1_CI_AS, where CI stands for case-insensitive, resulting in a different default sorting behavior.

Resolution Options

There are two main ways to address this issue depending on where you want to enforce case insensitivity: in the application layer or the database layer.

Option 1: Case-Insensitive Sorting in .NET Using StringComparer

If sorting is performed in memory or your codebase is small, you can apply case-insensitive ordering directly in LINQ using StringComparer.OrdinalIgnoreCase.

1
var listOfString = new List<string>
2
{
3
    "A", "a", "b", "B",
4
};
5

6
var orderedListOfString = listOfString
7
    .OrderBy(name => name, StringComparer.OrdinalIgnoreCase)
8
    .ToList();

Result

1
A
2
a
3
b
4
B

Advantages

No database schema changes required
Database remains provider-agnostic
Easy to implement in small codebases

Limitations

Requires updating multiple LINQ queries
Does not affect SQL-level ordering, indexes, or constraints
Inefficient for large datasets retrieved from the database

Option 2: Using PostgreSQL citext for Case-Insensitive Behavior

For applications originally designed around MSSQL’s case-insensitive behavior, modifying application code everywhere may be impractical. PostgreSQL provides the citext extension to support case-insensitive comparisons at the database level.

The citext (case-insensitive text) type behaves like text but compares values without considering case. This means queries, indexes, and constraints all become case-insensitive automatically.

Enabling citext in Entity Framework Core

To enable the extension when using EF Core:

1
protected override void OnModelCreating(ModelBuilder builder)
2
{
3
    base.OnModelCreating(builder);
4

5
    builder.HasPostgresExtension("citext");
6
}

After enabling the extension, you can alter an existing column:

1
ALTER TABLE testsorting
2
ALTER COLUMN name TYPE citext;

Once the column uses citext, PostgreSQL performs case-insensitive ordering:

1
SELECT name FROM testsorting ORDER BY name;

Result

1
a
2
A
3
a1
4
a11
5
a19
6
a2
7
a20
8
b
9
B

This behavior closely matches MSSQL’s default sorting and ensures consistent results across database queries.

Choosing the Right Approach

Use StringComparer.OrdinalIgnoreCase when:

Sorting is done in memory
You want to keep the database fully portable
Only a few queries require case-insensitive ordering

Use citext when:

Migrating from MSSQL and expecting the same behavior
Case-insensitive comparisons are required in SQL queries
You need case-insensitive indexes and unique constraints
You want to minimize changes to existing application code

Conclusion

The difference in default case sensitivity between MSSQL and PostgreSQL is a common source of confusion during database migrations. PostgreSQL’s strict case-sensitive ordering is technically correct but often unexpected for developers coming from MSSQL.

By either applying case-insensitive sorting in .NET using StringComparer.OrdinalIgnoreCase or enabling PostgreSQL’s citext extension, you can restore predictable and consistent sorting behavior across your application.

For most real-world migrations, using citext provides the cleanest and most maintainable solution because it preserves expected behavior without requiring widespread code changes.