Techbrifut.EntityFrameworkCore.PostgreSQL.UsefulExtensions 0.2.0

EntityFrameworkCore: PostgreSQL Useful Extensions

Features

• Case-insensitive string matching for PostgreSQL:
  • ILike, ILikeStartsWith, ILikeEndsWith, ILikeContains extension methods for use in LINQ-to-Entities queries, translated to PostgreSQL ILIKE.
• Case-insensitive equality:
  • EqualsLowerCase extension method for comparing strings in a case-insensitive manner.
• Dynamic query composition:
  • OrWhere extension method for combining multiple predicates with OR logic in LINQ queries.
• Conditional filtering:
  • WhereIf and OrWhereIf overloads to apply filters only when certain conditions are met (e.g., filter values are provided).

Case-insensitive String Matching (ILIKE) and Translation Details

This library provides extension methods for case-insensitive string matching in PostgreSQL using the ILIKE operator:

• ILike(pattern): Checks if the string matches the given SQL LIKE pattern, case-insensitively.
• ILikeStartsWith(value): Checks if the string starts with the given value, case-insensitively.
• ILikeEndsWith(value): Checks if the string ends with the given value, case-insensitively.
• ILikeContains(value): Checks if the string contains the given value, case-insensitively.

How it works:

• These methods are only supported in LINQ-to-Entities queries and are translated to SQL by a custom EF Core translator.
• The translation uses PostgreSQL's ILIKE operator with the ESCAPE '\' clause for safe pattern matching.
• Special characters (\, %, _) in the input are automatically escaped to prevent unintended wildcard matches.

Installation

Install the NuGet package:

dotnet add package Techbrifut.EntityFrameworkCore.PostgreSQL.UsefulExtensions

Example Usage

Below are real-world examples from the ExampleApp project.

1. DbContext Setup

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Npgsql;
using Techbrifut.EntityFrameworkCore.PostgreSQL.UsefulExtensions;

public sealed class AppDbContext : DbContext
{
    public DbSet<User> Users { get; init; } = null!;

    public AppDbContext(DbContextOptions options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
        base.OnModelCreating(builder);
    }

    public static AppDbContext Create()
    {
        string connectionString = new NpgsqlConnectionStringBuilder
        {
            Host = "localhost",
            Port = 5432,
            Username = "postgres",
            Password = "postgres",
            Database = "test_db"
        }.ConnectionString;

        var optionsBuilder = new DbContextOptionsBuilder()
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors()
            .UseNpgsql(connectionString)
            .UseUsefulExtensions();

        return new AppDbContext(optionsBuilder.Options);
    }
}

2. Entity Example

public sealed class User
{
    public Guid Id { get; init; } = Guid.NewGuid();
    public required string LastName { get; set; }
    public required string FirstName { get; set; }
    public string FullName { get; private set; } = null!;
}

3. Filter Record Example

public sealed record Filter(string? FirstName, string? LastName);

4. Case-insensitive Equality

await using var db = AppDbContext.Create();

var users = await db.Users.AsNoTracking()
    .Where(user => user.FirstName.EqualsLowerCase("alice"))
    .ToListAsync();

foreach (var user in users)
{
    Console.WriteLine(user.FullName);
}

Note: EqualsLowerCase translates to lower(column) = lower(value) in SQL, enabling true case-insensitive equality comparison.

5. Case-insensitive String Matching (ILIKE)

await using var db = AppDbContext.Create();

var users = await db.Users.AsNoTracking()
    .Where(user => user.FullName.ILike("%john%"))
    .ToListAsync();

foreach (var user in users)
{
    Console.WriteLine(user.FullName);
}

6. StartsWith / EndsWith / Contains (case-insensitive)

// StartsWith
var usersStart = await db.Users.AsNoTracking()
    .Where(user => user.LastName.ILikeStartsWith("thomp"))
    .ToListAsync();

// EndsWith
var usersEnd = await db.Users.AsNoTracking()
    .Where(user => user.LastName.ILikeEndsWith("son"))
    .ToListAsync();

// Contains
var usersContain = await db.Users.AsNoTracking()
    .Where(user => user.LastName.ILikeContains("il"))
    .ToListAsync();

7. Conditional Filtering (WhereIf)

var filter = new Filter(FirstName: "George", LastName: null);

var users = await db.Users.AsNoTracking()
    .WhereIfIsNotNullOrEmpty(filter.FirstName, user => user.FirstName.EqualsLowerCase(filter.FirstName!))
    .ToListAsync();

8. Dynamic OR Queries (OrWhereIf)

var filter = new Filter(FirstName: string.Empty, LastName: "White");
var specialFilter = new { IncludeAlice = true };

var users = await db.Users.AsNoTracking()
    .WhereIfIsNotNullOrEmpty(filter.LastName, user => user.LastName.EqualsLowerCase(filter.LastName!))
    .OrWhereIf(specialFilter.IncludeAlice, user => user.FirstName.EqualsLowerCase("Alice"))
    .ToListAsync();

Note: If OrWhere or OrWhereIf is used without a preceding Where, it behaves like a regular Where and starts a new predicate group.

SQL Translation Table

Escaping: backslash (\) is used as the escape character; existing \, %, _ are escaped.

Notes

• These extension methods are only supported in LINQ-to-Entities queries and will be translated to SQL.
• Do NOT use them in client-side code; they will throw NotSupportedException.
• Consider proper indexing (e.g., functional indexes on lower(column)) when using case-insensitive comparisons for performance.
• For ILIKE with leading wildcards (e.g., %value), regular B-Tree indexes won't be used. Consider trigram (pg_trgm) indexes for faster pattern searches.