Case-insensitive “LIKE” search with EF Core and PostgreSQL

When using EF Core with PostgreSQL for a case-insensitive “LIKE” search, you can leverage PostgreSQL-specific features to achieve the desired behavior. PostgreSQL supports the ILIKE operator, which performs a case-insensitive pattern matching operation. Here’s an example of how to perform a case-insensitive “LIKE” search using EF Core and PostgreSQL:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

public class MyEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("your_connection_string_here");
    }
}

class Program
{
    static void Main()
    {
        using (var dbContext = new MyDbContext())
        {
            string searchTerm = "john";

            // Perform case-insensitive "LIKE" search using EF Core and PostgreSQL
            var result = dbContext.MyEntities
                .Where(e => EF.Functions.ILike(e.Name, $"%{searchTerm}%"))
                .ToList();

            foreach (var entity in result)
            {
                Console.WriteLine($"Id: {entity.Id}, Name: {entity.Name}");
            }
        }
    }
}

In this example, we have an MyEntity class representing an entity with an Id and a Name. We configure a PostgreSQL database context using DbContext and specify the connection string.

To perform a case-insensitive “LIKE” search, we use the EF.Functions.ILike method provided by EF Core, passing the column name (e.Name) and the search term (%{searchTerm}%) as parameters within the LINQ query. The % characters act as wildcards, allowing for matching any substring.

The ILike method translates to the ILIKE operator in PostgreSQL, enabling case-insensitive pattern matching. The result is a list of entities that match the search term, regardless of case.

Remember to replace "your_connection_string_here" with the actual connection string to your PostgreSQL database.

With this approach, you can perform a case-insensitive “LIKE” search in PostgreSQL using EF Core, allowing you to retrieve matching records regardless of the case sensitivity.