Searching names by each word in search string

Question

I am trying to build a search within my ASP.NET MVC website. For example, I want to return the products that match the Items having any keywords matches within the search string. As an example, I have products such as,

DC 12V RGB 5050 5M LED STRIP
RGB 5050 LED 5 Meters Strip Kit with 24 Keys IR Remote Controller
LED Strip lights DC 12V 44 Keys IR Remote RGB Controller

Currently if I search for "rgb strip" i don't get any results from the search. If I search for "rgb strip", I needs to return the first two products which have 'rgb' and 'strip' keywords from the search text. Within current functionality, if i search for 'rgb' it returns all above 3 products. I needs to implement a mechanism which search products with contains each word within product names. But I'm not sure how to go about doing this. Can please anyone help me on this? Thank you.

The current search function:

public List SearchProducts(List categoryIDs, string searchTerm, decimal? from, decimal? to, string sortby, int? pageNo, int recordSize, bool activeOnly, out int count, int? stockCheckCount = null)
{
    var context = DataContextHelper.GetNewContext();

    var products = context.Products
                          .Where(x => !x.IsDeleted && (!activeOnly || x.IsActive) && !x.Category.IsDeleted)
                          .AsQueryable();

    if (!string.IsNullOrEmpty(searchTerm))
    {
        products = context.ProductRecords
                          .Where(x => !x.IsDeleted && x.Name.ToLower().Contains(searchTerm.ToLower()))
                          .Select(x => x.Product)
                          .Where(x => !x.IsDeleted && (!activeOnly || x.IsActive) && !x.Category.IsDeleted)
                          .AsQueryable();
    }

    if (categoryIDs != null && categoryIDs.Count > 0)
    {
        products = products.Where(x => categoryIDs.Contains(x.CategoryID));
    }
}

Based on @Rashik Hasnat Answer,

I have an issue here accessing Name attribute because Name attribute inherits from ProductRecords model, but the expression attribute is mapped to Products Model. Can you please help to fix this issue? Thank you.

user_6630084 · Answer

I would prefer to use the regular expression here, but unfortunately there is a limitation using it in LINQ to Entities. Therefore you can use DbFunctions.Like():

if (!string.IsNullOrEmpty(searchTerm))
{
    // Build the pattern for the Like() method
    var pattern = "%" + String.Join("%", searchTerm.Split(' ')) + "%";

    products = context.ProductRecords
                      .Where(x => !x.IsDeleted && DbFunctions.Like(x.Name, pattern))
                      .Select(x => x.Product)
                      .Where(x => !x.IsDeleted && (!activeOnly || x.IsActive) && !x.Category.IsDeleted)
                      .AsQueryable();
}

user_6400629 · Answer

You can do like below:

Split the search text to words
save the logics of linq expression in a variable (except for the string matching part)
Add a Contains expression for each words on the search term.

The code woulde look like below:

        if (!string.IsNullOrEmpty(searchTerm))
        {
            //split each word of the search string
            var searchWordList = searchTerm.Split(" "); 

            var expression = context.ProductRecords
                .Where(x => !x.IsDeleted && x.Name.ToLower().Contains(searchTerm.ToLower())).Select(x => x.Product)
                .Where(x => !x.IsDeleted && (!activeOnly || x.IsActive) && !x.Category.IsDeleted);

            foreach (var word in searchWordList)
            {
                // Add a condition for each word to be present
                expression = expression
                    .Where(x => x.Name.ToLower().Contains(word.ToLower())); 
            }

            products = expression
                .AsQueryable();
        }

Searching names by each word in search string

2 Answers