C
C#2mo 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)));
}
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
Lisa2mo 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.