SQL: combina query di ricerca anziché più viaggi DB

Mi ci è voluto un po 'di tempo per capire che usi Union nel tentativo di ordinare i dati per "forza della partita": prima quelli che corrispondono esattamente, poi quelli che corrispondono a casi diversi, ecc. Quando vedo Union s con predicati il mio Pavlov mente condizionata la traduce in OR s. Ho dovuto passare dal pensiero veloce al lento .

Quindi il problema è che non esiste un ordinamento prevedibile. Senza dubbio, le dichiarazioni concatenate Union producono un ordinamento finale deterministico, ma non è necessariamente l'ordine Union , perché ogni Union esegue anche un Distinct implicito. La regola generale è, se si desidera un ordinamento specifico, utilizzare i metodi OrderBy .

Detto questo, e prendendo ...

var result = entities
    .Where(e => e.Name.Contains(searchTerm))
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize).ToList();

... il risultato desiderato sembra essere ottenuto da:

var sortedEntities = result
    .OrderByDescending(e => e.Name == searchTerm)
    .ThenByDescending(e => e.Name.ToLower() == searchTerm.ToLower())
    .ThenByDescending(e => e.Name.StartsWith(searchTerm, StringComparison.OrdinalIgnoreCase))
    ... etc.

(discendente, perché i false ordini before vero)

Tuttavia, se ci sono più partite di pageSize l'ordine sarà troppo tardi. Se pageSize = 20 e l'elemento 21 è la prima corrispondenza esatta, questo elemento non sarà sulla pagina 1. Il che significa che l'ordine deve essere eseguito prima della paginazione.

Il primo passo sarebbe rimuovere la .ToList() dalla prima istruzione. Se lo rimuovi, la prima istruzione è un'espressione IQueryable e Entity Framework è in grado di combinare l'intera istruzione in un'unica istruzione SQL. Il prossimo passo sarebbe spostare Skip/Take alla fine della dichiarazione completa e sarà anche parte di SQL.

var result = entities.Where(e => e.Name.Contains(searchTerm));

var sortedEntities = result
    .OrderByDescending(e => e.Name == searchTerm)
    .ThenByDescending(e => e.Name.ToLower() == searchTerm.ToLower())
    .ThenByDescending(e => e.Name.StartsWith(searchTerm, StringComparison.OrdinalIgnoreCase))
    ... etc
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize).ToList();

Ma ora arriva un nuovo problema.

Poiché il confronto delle stringhe con StringComparison.OrdinalIgnoreCase non è supportato, Entity Framework passerà automaticamente alla valutazione lato client per una parte dell'istruzione. Tutti i risultati filtrati verranno restituiti dal database, ma la maggior parte degli ordini e di tutto il paging saranno eseguiti in memoria.

Potrebbe non essere un problema quando il filtro è stretto, ma molto brutto quando è largo. Quindi, alla fine, per fare ciò, devi rimuovere StringComparison.OrdinalIgnoreCase e sistemare con una forza di corrispondenza un po 'meno raffinata. Portandoci al

Risultato finale :

var result = entities.Where(e => e.Name.Contains(searchTerm));
var sortedEntities = result
    .OrderByDescending(e => e.Name == searchTerm)
    .ThenByDescending(e => e.Name.StartsWith(searchTerm))
    .ThenByDescending(e => e.Name.Contains($" {searchTerm} "))
    .ThenByDescending(e => e.Name.EndsWith(searchTerm))
    .ThenByDescending(e => e.Name.Contains(searchTerm))
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize).ToList();

Perché "meno raffinato"? Perché, in base ai commenti, le regole di confronto del database non sono sensibili al maiuscolo / minuscolo, pertanto SQL non è in grado di distinguere le corrispondenze esatte per caso senza aggiungere istruzioni COLLATE . Questo è qualcosa che non possiamo fare con LINQ.