Free-text search that hits the index

Q — the search box — is where naive filtering dies. WHERE Title LIKE '%josé%' misses Jose, mixed case, doubled spaces, and anything living in a sibling column. Mangling every column at query time (LOWER, REPLACE, …) can't use an index and still misses diacritics.

The workable answer flips the timing: normalize at save time, compare normalized to normalized at query time. This article shows both halves in the series' sample.

Save time: one attribute declares the column

Entities that want to be searchable declare a normalized column and name its sources:

public class Vehicle : IEntityWithSerial, IHasNormalizedContent, IArchivable
{
    // ...
    [MaxLength(256), Normalized(SourceProperties = [nameof(Model), nameof(Code)])]
    public string? NormalizedContent { get; set; }
}

A save-time normalizer (wired by UseDefaults()) computes it: diacritics stripped, punctuation and whitespace collapsed, case preserved by default. You never write to it, and it's an ordinary string column — which means it indexes like one. The sample's seed shows the effect nicely on the Brand side, where the same attribute feeds NormalizedTitle from Title:

The hyphen is gone. Hold that thought.

Query time: the built-in ?q=

The free-text filter is one of the global filters: for every entity with a NormalizedContent, it parses the search string with the same normalizer, producing per-keyword wildcard patterns, and applies each as a LIKE on the stored column:

var keywords = qHelper.Parse(so.Q);
foreach (var q in keywords)
    query = query.Where(x => EF.Functions.Like(x.NormalizedContent, q.QW));

Same treatment on both sides, so the input and the column meet in the middle:

curl "$URL/vehicles/search?q=fh16"       # count 1: TRK-001 (from Model "FH16 750")
curl "$URL/vehicles/search?q=sprinter"   # count 1: VAN-001

Multiple keywords AND together, and matching stays case-insensitive on the collations most databases default to (searching jose finds José; if you can't rely on your collation, configure the normalizer's Transform to a fixed casing). A parent can even fold its children's normalized text into its own NormalizedContent — normalize phone numbers consistently and a party becomes findable by any spelling of its number.

The same trick in your own filters

The Brand text filter from the basics is the branch we skipped there, and now it reads naturally — normalize the input the same way the column was normalized, using the injected IQKeywordHelper:

if (!string.IsNullOrWhiteSpace(so?.Brand))
{
    var brand = qHelper.ParseKeyword(so.Brand);
    query = query.Where(x => EF.Functions.Like(x.Brand!.NormalizedTitle!, brand.QW));
}

curl "$URL/vehicles?brand=mercedes"      # VAN-001, VAN-002

That's the hyphenless search matching Mercedes-Benz, because both sides normalized to Mercedes Benz. And here's the trap the helper exists to prevent: pass the raw input to LIKE against a normalized column and searches silently match nothing — mercedes-benz would look for a hyphen the column no longer has. Raw against normalized is the wrong pair, always.

When the provider dialect leaks

EF.Functions.Like is standard, but providers differ (PostgreSQL wants ILike for guaranteed case-insensitivity). The pipeline gives that a clean seam — swap the builder at registration, not the call sites:

_ = dbType == DataBaseTypes.PostgreSQL
    ? e.AddFilter<VehiclePostgresLikeQueryFilter>()   // EF.Functions.ILike(...)
    : e.AddFilter<VehicleLikeQueryFilter>();          // EF.Functions.Like(...)

One registration line per provider; everything else in the series stays identical.

What to take away

Search is a write-time feature. Declare the column, name its sources, and query time becomes a plain, indexable LIKE with both sides speaking the same normalized language. Next: the rest of the read pipeline — sorting, includes and paging.

Docs: Regira Entities — normalizing, Q keyword helpers, global filters.