C#C
C#•2y ago
Kamil Pisz

Search in database for more then one column (EFCore, Postgresql)

Hello
if (!string.IsNullOrWhiteSpace(query.Search))
 {
     var searchTxt = $"%{query.Search}%";
     queryDb = queryDb.Where(e =>
         (!string.IsNullOrEmpty(e.Name.FirstName) && EFCore.Functions.ILike(e.Name.FirstName, searchTxt)) ||
         (!string.IsNullOrEmpty(e.Name.LastName) && EFCore.Functions.ILike(e.Name.LastName, searchTxt)));
 }


in Employee table i have column: FirstName and LastName
those examples in search query should work
  • John
    • Smith
    • Smith John
    • John Smith
    • John S
my first thought (from gpt chat 😉 ) is to split query like :

var searchTerms = query.Search.Split(' '); // split the search phrase into individual words
    queryDb = queryDb.Where(e =>
        searchTerms.Any(term => 
            EFCore.Functions.ILike(e.Name.FirstName ?? string.Empty, $"%{term}%") || 
            EFCore.Functions.ILike(e.Name.LastName ?? string.Empty, $"%{term}%")
        )
    );

What u think about this ?

Problem, what if add filter for example Location but dont want split search query then
Was this page helpful?