C#•4mo ago

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)));
 }

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}%")
        )
    );

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

1 Reply

Lisa•4mo ago

what's stopping you from just making your own search function? Take in the record and the search term, feed it into a function, return true or false wether it matches. .Where(e => IsMatchingEmployee(e, searchTerm)) and then you can chain on whatever else you want to do, eg location.

Gaming

Programming

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