Benny Sutton is 4 hire - just click here

Database Search algorithm

Here's a query you can run directly in LINQPad demonstrating a search algorithm

It returns either the results for whole search term (if successful) or AND all the words in the search term and try that

Select language > C# Statements on the LINQpad menu, connect your database and specify the table/fields in the code below

             

     if (CatId.HasValue)
        query = query.Where(r => r.CategoryID == CatId);
      if (!String.IsNullOrEmpty(SearchTerm)){
      SearchTerm.Dump();
                 string[] keywords = SearchTerm.Split(' ');
            int wordcount = keywords.Length;
            var testquery = query.Where(r => r.Description.Contains(SearchTerm));
            int mycount = testquery.Select(r => r.CategoryID).Count();
            mycount.Dump(" result count for whole search term");
                    if (mycount >= 1) 
                    { // whole search term returns records
                        query = query.Where(r => r.Description.Contains(SearchTerm));
                    }
                    else if (mycount == 0 && wordcount == 1)
                    { // then FAIL - no records and one word so no point in going on
                        query = query.Where(r => r.Description.Contains(SearchTerm));
                    }
                    else if (mycount == 0 && wordcount >= 2)
    { // more than one word so try to AND or OR all words
        foreach (string keyword in keywords)
            query = query.Where(p => p.Description.Contains(keyword));
        testquery = query;
        mycount = testquery.Select(r => r.CategoryID).Count();
        mycount.Dump(" result count for AND words");
    }
    }  
		     query = query.Take(20);

    query.Dump();