Search and compare input string from .Net Core app to multiple SQL columns

Question

I'm sorry if the way I asked the question is confusing. I have a .Net Core E-commerce project that sells music albums. I want to add a search bar so that the user can search for albums by title, artist or genre. Those are stored in a SQL db. I can't seem to find a way to compare the string that the user inputs to all three of those, only one. Here's what I have:

public ViewResult Search(string searchString)
        {
            string _searchString = searchString;
            IEnumerable<Album> albums;


            if (string.IsNullOrEmpty(_searchString))
            {
                albums = _albumRepository.Albums.OrderBy(p => p.AlbumId);
            }
            else 
            {
                albums = _albumRepository.Albums.Where(p => p.Name.ToLower().Contains(_searchString.ToLower()));

            }

            return View("~/Views/Album/List.cshtml", new AlbumListViewModel { Albums = albums});
        }

Look into [full-text search](https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-2017). These queries will not perform if you have a significant amount of data. — Jesse de Wit, Mar 05 '19 at 18:34

score 0 · Accepted Answer · answered Mar 05 '19 at 18:03

It's a little difficult to answer, given that we have no visibility into your actual model(s) here, but generally speaking, you just need to use the logical OR operator:

albums = _albumRepository.Albums.Where(p =>
    p.Name.ToLower().Contains(_searchString.ToLower()) ||
    p.Artist.ToLower().Contains(_searchString.ToLower()) ||
    p.Genre.ToLower().Contains(_searchString.ToLower())
);

If Artist/Genre are actually reference props, you'd just drill into them, i.e. p.Artist.Name.ToLower().Contains(...), etc.

exactly what I needed, I tried so manu things but never thought to try something so simple — Troi, Mar 05 '19 at 18:15

score 0 · Answer 2 · answered Mar 05 '19 at 18:34

If you have a lot of conditions, you can move the code to a method, to improve readability/re-use:

albums = _albumRepository.Albums.Where(p => p.Match(searchString))

Then create the extension method for type p (Albums class) or just add this on the class itself if it makes more sense to be there in your project.

public static class AlbumsExtensions
{
    public static bool Match(this Album album, string search)
    {

        // whatever complex logic you have stays here

        var search = searchString.ToLower();

        var result = p.Name.ToLower().Contains(search) ||
                     p.Artist.ToLower().Contains(search) ||
                     p.Genre.ToLower().Contains(search);

        return result ;
    }
}

Last but not least, note that string compare is not trivial if you support unicode and several languages.

For more ingormation, take a look at (for example): Case insensitive 'Contains(string)'

Search and compare input string from .Net Core app to multiple SQL columns

2 Answers2