locked
"Relaxed" Search Question RRS feed

  • Question

  • Hey guys! Building an app using Lightswitch to automate inventory and ordering. Running into a problem with searching for inventory by description. In SQL, I would concat LIKE statements using the OR operator, but in Lightswitch, I'm at a complete loss. My google-foo is quite good, but I'm coming up empty.
    Example:
    I have a table called "Items" The field I'd like to search is called "Description" My query has a parameter called "searchParam"
    So for an example item, its description is "Jumbo Wax Ring with Horn".
    If I search: "Wax Ring" or "Jumbo Wax", I'll get the intended item. However, if I were to search "Jumbo Ring" or "Wax Horn", I'll get no result. The search is very literal and I can't find an elegant way around this problem. Any suggestions would be greatly appreciated!

    Here's an example of the way I would've solved this problem (right or wrong) using SQL:

    searchWords = Split(txt_Search)
            If UBound(searchWords) = 0 Then
                strSQL = "SELECT * FROM Items WHERE Items.Description LIKE '*" & txt_Search & "*' OR Items.Barcode LIKE '*" & txt_Search & "*'"
            Else
                strSQL = "SELECT * FROM Items WHERE Items.Description LIKE '*" & txt_Search & "*' OR Items.Barcode LIKE '*" & searchWords(0) & "*'"
                Dim lCounter        As Long
                For lCounter = (LBound(searchWords) + 1) To UBound(searchWords)
                    strSQL = strSQL & " " & searchType & " Items.Description LIKE '*" & searchWords(lCounter) & "*'"
                Next
            End If

    In the above code, the variable searchType was either "AND" or "OR" as selected by the user.  In my new circumstance here, I would like the effective the operator to always be "OR".

    I have tried all sorts of things to manipulate the pre-process query into allowing me to split a search phrase, iterate through them and add the results to the query result, but I can't figure out how to do it.

    Tuesday, December 23, 2014 12:36 PM

Answers

  • Something like this should also work.

    char[] separators = { ',', ';', ' ' };
    
    if (!String.IsNullOrWhiteSpace(searchWords)
    {
           string[] words = searchWords.Split(separators, StringSplitOptions.RemoveEmptyEntries);
           query = query.Where(a => words.Any(b => a.Description.Contains(b)));
    }


    • Edited by Hessc Monday, December 29, 2014 2:58 PM
    • Proposed as answer by joshbooker Monday, December 29, 2014 3:26 PM
    • Marked as answer by StefanVon Monday, December 29, 2014 4:20 PM
    Monday, December 29, 2014 2:57 PM
  • VB users, I feel your pain.  Here you go:

    Dim separators As Char() = {",", ";", " "}
    Dim words As String() = searchWords.Split(separators, StringSplitOptions.RemoveEmptyEntries)
    query = query.Where(Function(a) (words.Any(Function(b) (a.Description.Contains(b)))))

    • Proposed as answer by joshbooker Monday, December 29, 2014 4:01 PM
    • Marked as answer by StefanVon Monday, December 29, 2014 4:17 PM
    Monday, December 29, 2014 3:59 PM

All replies

  • Modify the "AND" to "OR" in the SQL code, is it suitable for your requirement?

    Monday, December 29, 2014 8:47 AM
  • Have a look at this:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/00306864-e50b-4f1d-bfc9-84e646216ef3/search-query-for-multiple-string-values-within-the-same-property?forum=lightswitch

    You could try looping like so:

    string[] searchWords = txt_Search.Split(' ');    
    foreach (string word in searchWords)
         {
             query = query.Where(x => x.Description.Contains(word));
         }

    HTH,

    Josh

    Monday, December 29, 2014 2:35 PM
  • Something like this should also work.

    char[] separators = { ',', ';', ' ' };
    
    if (!String.IsNullOrWhiteSpace(searchWords)
    {
           string[] words = searchWords.Split(separators, StringSplitOptions.RemoveEmptyEntries);
           query = query.Where(a => words.Any(b => a.Description.Contains(b)));
    }


    • Edited by Hessc Monday, December 29, 2014 2:58 PM
    • Proposed as answer by joshbooker Monday, December 29, 2014 3:26 PM
    • Marked as answer by StefanVon Monday, December 29, 2014 4:20 PM
    Monday, December 29, 2014 2:57 PM
  • Hi 

    you could also use SQLFunctions in PreprocessorQuery as in thread

    https://social.msdn.microsoft.com/forums/vstudio/en-US/5ede2db0-a4f9-4178-84c0-fabf4d3a57fa/how-to-generate-the-sql-like-linq-in-ls-query

    I have used it like this:

     partial void AccountSearchExtended_PreprocessQuery(string SearchString, ref IQueryable<AccountSearchView> query)
            {
                if (SearchString != null && SearchString.Length > 0)
                {
                    String searchvalue = SearchString;
                    if (searchvalue == null)
                        searchvalue = "%";
                    else
                        searchvalue = "%" + searchvalue.Replace(" ", "%") + "%";
    
                    query = from q in query
                            where (SqlFunctions.PatIndex(searchvalue, SqlFunctions.StringConvert((decimal)q.AccountID, 10, 0)) > 0
                                   || SqlFunctions.PatIndex(searchvalue, q.Address) > 0
                                   || SqlFunctions.PatIndex(searchvalue, q.Party) > 0
                                   || SqlFunctions.PatIndex(searchvalue, q.SourceKey) > 0
                                   || SqlFunctions.PatIndex(searchvalue, q.SSN) > 0
                                   || SqlFunctions.PatIndex(searchvalue, q.Brand) > 0)
                            select q;
                }
                
            }

    Monday, December 29, 2014 3:00 PM
  • In reply to Josh Booker

    Thank you brother!  This is the closest I have come to a viable solution.  I am using VB, so this is the translation I used:

                Dim searchParamArray() As String = Split(searchParam, " ")
                For Each word In searchParamArray
                    query = From x In query Where x.Description.Contains(word)
                Next

    Now, when I type "jumbo wax", "wax ring", "ring wax", or variations of any of the words in the description, I get the intended result.  

    However, I still have a problem if the user searches using a word I don't expect.  For instance:  Item description is "Wax Ring - Jumbo with Horn".  If the user searches "wax seal", no result will be given.  The solution you have proposed does a wonderful job of loosening up the search terms so that order does not matter, but I still can't get an "OR" result this way.  Is there a simple adjustment to this loop that could create this?


    • Edited by StefanVon Monday, December 29, 2014 3:42 PM
    Monday, December 29, 2014 3:29 PM
  • I could be wrong but believe Hessc's use of .any() in a single .where would have the advantage of the 'or' search that you are looking for.
    Monday, December 29, 2014 3:30 PM
  • In reply to FreLarSol

    If I'm not mistaken, it looks like your method allows me to search multiple columns using the same search string.  I tried patindex to search using multiple words, but it also doesn't accept (as far as I can tell) multiple expressions in an "OR"-like expression.  Meaning  like this:

    patindex(%wax% OR %seal%, q.description)


    • Edited by StefanVon Monday, December 29, 2014 3:43 PM
    Monday, December 29, 2014 3:33 PM
  • In reply to HessC

    Thank you very much for the suggestion, but I"m having a hard time translating into VB.  I got this far:

                Dim separators() As Char = {",", ";", " "}
                If Not (String.IsNullOrWhiteSpace(searchParam)) Then
                    Dim words() As String = searchParam.Split(separators, StringSplitOptions.RemoveEmptyEntries)


    • Edited by StefanVon Monday, December 29, 2014 3:43 PM
    Monday, December 29, 2014 3:40 PM
  • I could be wrong but believe Hessc's use of .any() in a single .where would have the advantage of the 'or' search that you are looking for.
    I wonder this myself, I'm just having trouble implementing this in VB
    Monday, December 29, 2014 3:44 PM
  • VB syntax is a little different.  Try something like this:

    query = query.Where(Function(a) (words.Any(Function(b) (a.Description.Contains(b))))) 

    HTH,

    Josh


    • Edited by joshbooker Monday, December 29, 2014 4:01 PM
    Monday, December 29, 2014 3:59 PM
  • VB users, I feel your pain.  Here you go:

    Dim separators As Char() = {",", ";", " "}
    Dim words As String() = searchWords.Split(separators, StringSplitOptions.RemoveEmptyEntries)
    query = query.Where(Function(a) (words.Any(Function(b) (a.Description.Contains(b)))))

    • Proposed as answer by joshbooker Monday, December 29, 2014 4:01 PM
    • Marked as answer by StefanVon Monday, December 29, 2014 4:17 PM
    Monday, December 29, 2014 3:59 PM
  • Hessc, your VB translation has been marked as the answer.  Thank you to everyone for your help and input and especially to Hessc for the final answer.  You guys are awesome.
    Monday, December 29, 2014 4:20 PM
  • No problem.  As you can tell from the thread Josh linked, this was something that I had a lot of trouble with a while back.  Glad it can help you.
    Monday, December 29, 2014 4:40 PM
  • It's funny you mention that; I had looked at that thread over and over again trying to extract my solution from it, but I simply could not get any of it to work for me.  If you or anyone out there can explain why this works, I'd love it to understand it better:

    query = query.Where(Function(a) (words.Any(Function(b) (a.Description.Contains(b)))))

    Monday, December 29, 2014 4:52 PM
  • Hi Stefan,

    Linq is not my strong point, but I'll try to make some sense of it to the best of my understanding. 

    The .Where() method is executed for each record and returns those records where the expression evaluates to true. 

    So in

    Where(function(a),

    a is the record (Item entity, in your case), thus a.Description refers to the Description property of the Item entity.

    The .Any() method iterates a collection and returns true if any of the iterations returns true.  So in

    words.Any(Function(b),

    words is the array and b is the member of the array for the respective iteration.

    So the pseudo code is something like this:

    for each entity (a), for each words(b) return the entity if (a.Description contains b)=true

    More info:

    Additional usages of .Where further filter the collection so that's why my loop suggestion did not provide the 'OR' behavior you wanted.  Since the loop resulted in something like:

    query.Where().Where().Where() which results in an 'AND' search.

    Hessc's example using.Any() results in the "OR' behavior because of the single .Where() expression.

    Pretty sure I stopped making sense a while ago.  ;-)

    HTH,

    Josh

    Monday, December 29, 2014 6:12 PM
  • Hi Stefan,

    Linq is not my strong point, but I'll try to make some sense of it to the best of my understanding. 

    The .Where() method is executed for each record and returns those records where the expression evaluates to true. 

    So in

    Where(function(a),

    a is the record (Item entity, in your case), thus a.Description refers to the Description property of the Item entity.

    The .Any() method iterates a collection and returns true if any of the iterations returns true.  So in

    words.Any(Function(b),

    words is the array and b is the member of the array for the respective iteration.

    So the pseudo code is something like this:

    for each entity (a), for each words(b) return the entity if (a.Description contains b)=true

    More info:

    Additional usages of .Where further filter the collection so that's why my loop suggestion did not provide the 'OR' behavior you wanted.  Since the loop resulted in something like:

    query.Where().Where().Where() which results in an 'AND' search.

    Hessc's example using.Any() results in the "OR' behavior because of the single .Where() expression.

    Pretty sure I stopped making sense a while ago.  ;-)

    HTH,

    Josh

    That explanation is better than I could have asked for.  Knowing why something works is important to me.  If I ever run into another specific need with a query, knowing why something works the way it does allows you to play around with it a little.  Thanks again!
    Monday, December 29, 2014 8:08 PM
  • Josh's explanation is good.  I will add that if you wanted to do an AND instead of OR, use the same code and replace .Any() with .All().  .All() is also good for constructing a query that excludes everything from a parameter array.  For example:

    query = query.Where(Function(a) (words.All(Function(b) (Not a.Description.Contains(b)))))
    
    
    This would return all records where the description does NOT contain any of the words in searchWords array.

    Tuesday, December 30, 2014 2:00 AM
  • You could rewrite the search expression with the same column name like (in C#):

    		query = from q in query
                            where (SqlFunctions.PatIndex("%wax%", q.description) > 0
                                   || SqlFunctions.PatIndex("%seal%", q.description) > 0)
                            select q;

    Tuesday, December 30, 2014 5:40 AM
  • Josh's explanation is good.  I will add that if you wanted to do an AND instead of OR, use the same code and replace .Any() with .All().  .All() is also good for constructing a query that excludes everything from a parameter array.  For example:

    query = query.Where(Function(a) (words.All(Function(b) (Not a.Description.Contains(b)))))
    
    This would return all records where the description does NOT contain any of the words in searchWords array.

    Thanks for this.  I've now implemented a combination of both depending on a true/false value selected by the user if they want the search to be "strict" or not.

            Private Sub qry_ItemsSearchByDescription_PreprocessQuery(txt_search As String, searchStrict As System.Nullable(Of Boolean), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Item))
                If Not String.IsNullOrWhiteSpace(txt_search) Then
    
                    Dim separators As Char() = {",", ";", " "}
                    Dim words() As String = txt_search.Split(separators, StringSplitOptions.RemoveEmptyEntries)
    
                    If searchStrict = True Then
                        query = query.Where(Function(a) (words.All(Function(b) (a.Description.Contains(b)))))
                    Else
                        query = query.Where(Function(a) (words.Any(Function(b) (a.Description.Contains(b)))))
    
                    End If
    
                Else
                    query = query
                End If
    
    
    
            End Sub
    Ideally, I'd like the preprocess query to do this selection automatically.  Meaning, if .all() method returns no records, use .any() 

    Wednesday, December 31, 2014 7:24 PM
  • You should be able to do that.  Remove the searchStrict parameter and try this (not tested but should be close):

    Private Sub qry_ItemsSearchByDescription_PreprocessQuery(txt_search As String, ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Item))
    
    If Not String.IsNullOrWhiteSpace(txt_search) Then
    Dim separators As Char() = {",", ";", " "}
    Dim words() As String = txt_search.Split(separators, StringSplitOptions.RemoveEmptyEntries)
    
    Dim defaultQuery = query.Where(Function(a) (words.All(Function(b) (a.Description.Contains(b)))))
                    
    'Check for a result (we only need one result to test)
    
    Dim result = defaultQuery.FirstOrDefault()
    
    If (result IsNot Nothing) Then
    query = defaultQuery
    Else
    query = query.Where(Function(a) (words.Any(Function(b) (a.Description.Contains(b)))))
    End If
    End If

    Wednesday, December 31, 2014 8:31 PM
  • @FreLarSol,

    Thanks for the SqlFunctions solution.  I have never used SqlFunctions like that.  Do you notice a performance gain or is it just preference?

    Wednesday, December 31, 2014 8:36 PM
  • You could rewrite the search expression with the same column name like (in C#):

    		query = from q in query
                            where (SqlFunctions.PatIndex("%wax%", q.description) > 0
                                   || SqlFunctions.PatIndex("%seal%", q.description) > 0)
                            select q;

    The "where" statement can't be generated dynamically though, can it? (Based on the number of strings in the array)
    Wednesday, December 31, 2014 9:24 PM
  • You should be able to do that.  Remove the searchStrict parameter and try this (not tested but should be close):

    Private Sub qry_ItemsSearchByDescription_PreprocessQuery(txt_search As String, ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Item))
    
    If Not String.IsNullOrWhiteSpace(txt_search) Then
    Dim separators As Char() = {",", ";", " "}
    Dim words() As String = txt_search.Split(separators, StringSplitOptions.RemoveEmptyEntries)
    
    Dim defaultQuery = query.Where(Function(a) (words.All(Function(b) (a.Description.Contains(b)))))
                    
    'Check for a result (we only need one result to test)
    
    Dim result = defaultQuery.FirstOrDefault()
    
    If (result IsNot Nothing) Then
    query = defaultQuery
    Else
    query = query.Where(Function(a) (words.Any(Function(b) (a.Description.Contains(b)))))
    End If
    End If

    First try results in the search always returning the .any() results, but I'll dig into this a little more and see if I can't figure out why.  
    Wednesday, December 31, 2014 9:25 PM