Details

Feature Request: Extensibility Point for Provider-Specific Case-Insensitive String Operations (e.g., PostgreSQL ILIKE)

🐛 Problem

Gridify currently implements case-insensitive string filtering using .ToLower() / .ToUpper().

When using Gridify with EF Core and PostgreSQL/Npgsql, a case-insensitive substring filter is translated to SQL similar to:

WHERE lower(op_series) LIKE @value

This is correct from a LINQ semantics perspective, but PostgreSQL provides a native ILIKE operator for case-insensitive pattern matching, which is much more efficient when combined with specific indexes.

Context & Index Configuration

In our application, we use PostgreSQL GIN indexes with gin_trgm_ops for substring searches, configured through EF Core Fluent API:

d.HasIndex(x => x.ItemCatNum)
    .HasMethod("gin")
    .HasOperators("gin_trgm_ops")
    .HasDatabaseName("ix_orders_item_cat_num_trgm_gin");

d.HasIndex(x => x.OpSeries)
    .HasMethod("gin")
    .HasOperators("gin_trgm_ops")
    .HasDatabaseName("ix_orders_op_series_trgm_gin");

d.HasIndex(x => x.ItemName)
    .HasMethod("gin")
    .HasOperators("gin_trgm_ops")
    .HasDatabaseName("ix_orders_item_name_trgm_gin");

Gridify Integration

The relevant part of the Gridify integration looks like this:

using Gridify.EntityFramework;

public static partial class GridifyExtensions
{
    public static async Task<QueryablePaging<T>> GridifyQueryableAsync<T>(
        this IQueryable<T> query,
        IGridifyQuery gridifyQuery,
        IGridifyMapper<T>? mapper)
    {
        query = query.ApplyFiltering(gridifyQuery, mapper);
        var count = await query.CountAsync();
        query = query.ApplyOrdering(gridifyQuery, mapper);
        query = query.ApplyPaging(gridifyQuery);

        return new QueryablePaging<T>(count, query);
    }
}

The Gridify mapper marks the relevant string fields as case-insensitive:

public sealed class OrderMapsGridify : GridifyMapper<OrderMap>
{
    public OrderMapsGridify()
    {
        AddMap("opSeries", q => q.OpSeries, caseInsensitive: true);
        AddMap("itemCatNum", q => q.ItemCatNum, caseInsensitive: true);
        AddMap("itemName", q => q.ItemName, caseInsensitive: true);
    }
}

The Issue in Action

Given this request:

{
  "page": 1,
  "pageSize": 100,
  "filter": "(opSeries=*aBcD|itemCatNum=*aBcD|itemName=*aBcD)",
  "orderBy": "itemCatNum, opSeries"
}

Gridify currently produces an expression that EF Core + Npgsql translates approximately to:

WHERE
    (
        lower(op_series) LIKE @Value_contains
        OR lower(item_cat_num) LIKE @Value2_contains
        OR lower(item_name) LIKE @Value3_contains
    )

🔍 Expression Tree Generated by Gridify

I also checked the expression tree directly after applying the Gridify filter:

query = query.ApplyFiltering(request.FilteredQuery, gridifyMapper);
var expression = query.Expression.ToString();

The resulting expression contains ToLower() directly:

[Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression]
    .AsNoTracking()
    .Where(__OrderMap =>
        (((__OrderMap.Details.OpSeries.ToLower().Contains(value(GridifyDisplayClass).Value)
        OrElse __OrderMap.Details.ItemCatNum.ToLower().Contains(value(GridifyDisplayClass).Value))
        OrElse __OrderMap.Details.ItemName.ToLower().Contains(value(GridifyDisplayClass).Value)))

Conclusion: This confirms that ToLower() is introduced by Gridify when building the expression tree, before EF Core/Npgsql translates the expression to SQL. Therefore, a provider-specific extension point at the Gridify expression-building level could allow PostgreSQL/Npgsql integrations to generate a provider-specific representation for case-insensitive string operations instead of wrapping the column in ToLower().

✅ Expected Behavior

It would be highly useful to have an extensibility point that allows a database provider or integration to customize case-insensitive string operations.

For PostgreSQL/Npgsql, the same Gridify query could then be translated to:

WHERE
    (
        op_series ILIKE @Value_contains
        OR item_cat_num ILIKE @Value2_contains
        OR item_name ILIKE @Value3_contains
    )

Constraints:

• The Gridify query syntax and mapper API should remain completely unchanged:

  AddMap("itemName", q => q.ItemName, caseInsensitive: true);

• The provider-specific implementation would only change how the case-insensitive operation is represented in the generated expression.

💡 Proposed Direction

Introduce an extension point for provider-specific case-insensitive string operations, while keeping the current behavior as the default fallback.

Conceptually, Gridify could allow an integration to customize operations such as:

• Contains
• StartsWith
• EndsWith
• Other case-insensitive string comparisons

This would allow an EF Core/Npgsql integration to use PostgreSQL-native operations such as ILIKE, while other providers (like SQL Server) could keep using the current .ToLower() / .ToUpper() approach.

Main Goal: Preserve the existing Gridify API and filtering syntax while allowing provider-specific integrations to generate more appropriate and optimized database expressions.

🚀 Why This Matters

The current generated SQL is logically correct, but it is not always the most appropriate representation for a specific database provider.

1. Native Operators: PostgreSQL provides native case-insensitive pattern matching through ILIKE.
2. Index Utilization: PostgreSQL applications frequently use GIN indexes with gin_trgm_ops for substring searches. Wrapping a column in lower() prevents the database from utilizing these specific trigram indexes efficiently.
3. Performance: Being able to preserve the raw column expression and use a provider-specific operator gives the underlying database provider the flexibility to properly optimize the query and use indexes.

The proposed change would make Gridify much more extensible for database-specific string operations without introducing PostgreSQL-specific (or any other DB-specific) behavior into the core library.