Understanding EF.Functions.Like and ILike in Entity Framework Core

For string-based searches involving patterns or partial matches, standard C# string methods can lead to suboptimal query translation. This often results in client-side evaluation, where data is loaded into memory unnecessarily. EF.Functions.Like and EF.Functions.ILike address these issues by mapping directly to efficient SQL operators.

These functions ensure that pattern-matching queries execute on the database server, avoiding the overhead of fetching excess data. Like corresponds to the SQL LIKE operator for case-sensitive or collation-dependent matching. ILike provides a case-insensitive alternative, mapping to ILIKE in providers like PostgreSQL or emulated equivalents elsewhere.

They exist to handle wildcard patterns (% for any sequence of characters, _ for a single character) efficiently, replacing C# methods that might not translate well to SQL.

Problems They Address

Client-side evaluation occurs when EF Core cannot convert LINQ to SQL, such as with complex string operations. For example, using string.Contains, StartsWith, or EndsWith on database columns can pull all rows to the client if not optimized.

Case sensitivity is another issue: methods like ToLower() or ToUpper() applied in LINQ may prevent index usage or cause inconsistencies across databases.

Developers rely on Like and ILike for server-side wildcard searches, ensuring scalability for large datasets.

EF.Functions.Like: Functionality and Replacements

Like performs pattern matching with database-specific case handling.

It replaces methods like:

• string.Contains(“foo”) → EF.Functions.Like(column, “%foo%”)
• string.StartsWith(“foo”) → EF.Functions.Like(column, “foo%”)
• string.EndsWith(“foo”) → EF.Functions.Like(column, “%foo”)
• Custom patterns, e.g., matching exactly three characters: EF.Functions.Like(column, ”___”)

Example showing replacement:

using Microsoft.EntityFrameworkCore;

// Inefficient: May evaluate client-side or use non-optimal SQL
var results1 = context.Users
    .Where(u => u.Name.Contains("John"))
    .ToList();

// Efficient: Translates to SQL LIKE '%%John%%'
var results2 = context.Users
    .Where(u => EF.Functions.Like(u.Name, "%John%"))
    .ToList();

// Replacement for StartsWith
var results3 = context.Users
    .Where(u => EF.Functions.Like(u.Name, "J%"))
    .ToList();

// Custom pattern: Names with 'a' as second character
var results4 = context.Users
    .Where(u => EF.Functions.Like(u.Name, "_a%"))
    .ToList();

Generated SQL for results2: SELECT … WHERE [u].[Name] LIKE N’%John%’.

EF.Functions.ILike: Case-Insensitive Matching and Replacements

ILike ignores case, ideal for user inputs.

It replaces combinations like:

• column.ToLower().Contains(“foo”) → EF.Functions.ILike(column, “%foo%”)
• string.Equals(column.ToUpper(), “FOO”) → EF.Functions.ILike(column, “foo”)
• Case-insensitive StartsWith: column.ToLower().StartsWith(“foo”) → EF.Functions.ILike(column, “foo%”)

Example showing replacement:

using Microsoft.EntityFrameworkCore;

// Inefficient: ToLower() may prevent index use, potential client eval
var results1 = context.Products
    .Where(p => p.Description.ToLower().Contains("widget"))
    .ToList();

// Efficient: Translates to case-insensitive LIKE (e.g., ILIKE in Postgres)
var results2 = context.Products
    .Where(p => EF.Functions.ILike(p.Description, "%widget%"))
    .ToList();

// Replacement for case-insensitive equality
var results3 = context.Products
    .Where(p => EF.Functions.ILike(p.Category, "electronics"))
    .ToList();

// Case-insensitive EndsWith
var results4 = context.Products
    .Where(p => EF.Functions.ILike(p.Description, "%device"))
    .ToList();

Generated SQL in PostgreSQL for results2: SELECT … WHERE “Description” ILIKE ‘%widget%’.

Internal Differences

• Case Sensitivity: Like follows database collation (e.g., case-insensitive in SQL Server defaults). ILike forces insensitivity, possibly using ILIKE.
• Query Translation: Both ensure server-side execution. ILike may add function calls, affecting index eligibility.
• Provider Support: Like is universal; ILike is native in PostgreSQL, emulated in others.
• Patterns: Both support % and _ wildcards.

Use Cases

Use Like for case-dependent searches:

• Matching product codes: EF.Functions.Like(Code, “ABC%”)

Use ILike for user-facing, case-agnostic queries:

• Search bars: EF.Functions.ILike(Name, “%query%”)

Code example in a search method:

public List<User> SearchUsers(string query)
{
    return context.Users
        .Where(u => EF.Functions.ILike(u.Name, $"%{query}%") ||
                    EF.Functions.ILike(u.Email, $"%{query}%"))
        .ToList();
}

Performance Impacts

Patterns starting with % (e.g., “%foo%”) cause table scans, ignoring indexes. Prefix patterns (“foo%”) use indexes if available.

ILike’s case-insensitivity can add overhead (e.g., LOWER wrapping), preventing index scans unless functional indexes exist.

On large tables (millions of rows), scans lead to high CPU/IO; test with EXPLAIN or query plans.

Code to benchmark:

var stopwatch = Stopwatch.StartNew();
var results = context.LargeTable
    .Where(t => EF.Functions.Like(t.Field, "%pattern%"))
    .ToList();
Console.WriteLine($"Time: {stopwatch.ElapsedMilliseconds} ms");

Performance vs. Query Trade-Offs

Server-side execution reduces data transfer but complex patterns slow queries. Trade-offs:

• Flexibility: Wildcards allow broad searches but degrade performance.
• Alternatives: Use Equals for exact matches (faster, index-friendly).
• Full-text search (EF.Functions.Contains) for better performance on text-heavy fields.
• Client-side: Simpler code but poor scaling; avoid for production.

Optimize by indexing columns and restricting wildcards.