Full Search testing in LINQ (custom filter query)

Question

I have a table that has a list of restaurant names and links to another table that holds the cuisine type.

I want to provide a search box on my web page that when typing, each word is searched for in the database and results returned. I was looking for a solution that doesn't involve setting up sql full text search as I want to be able to test this using Linq to SQL.

From my old code I have a function which creates a query filter given the input text and returns all the results based on that.

Private Function SetupQuery(ByVal searchText As String) As String
    Dim searchFields As New List(Of String)

    searchFields.Add("Name")
    searchFields.Add("Postcode")
    searchFields.Add("Cuisine")

    Dim firstCol As Boolean = True

    Dim a() As String
    Dim j As Integer
    a = searchText.Trim.Split(" ")

    Dim filter As String = ""

    Dim compareString As String
    For Each col As String In searchFields
        For j = 0 To a.GetUpperBound(0)
            compareString = a(j).ToUpper()
            compareString = compareString.Trim()

            If firstCol Then
                filter = filter & col & " LIKE '" & compareString & "%' "
                firstCol = False
            Else
                filter = filter & " or " & col & " LIKE '" & compareString & "%' "
            End If
        Next
    Next

    Return filter
End Function

This has the search fields hard coded and then loops through each one and each word in the search text to construct an OR LIKE filter.

I'm sure I could then use this in my LINQ code but it doesn't seem like an elegant solution especially since the columns are hard coded and not used how LINQ uses them.

Could anyone recomend a better way to do this or some tips in what direction to go with this?

Thanks

Answer 1

I don't how know this will affect your testing, but LinqtoSQL can be made to work with full-text indexing with a few wiggles:

• http://stackoverflow.com/questions/67706/linqtosql-and-full-text-search-can-it-be-done
• http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx

Answer 2

This probably isn't the best way to do this, but if you're always getting the search text as string split into an array of three then you could try using this:

from t in temp
where t.Name == a(0)
|| t.Postcode == a(1)
|| t.Cuisine == a(2)
select t

I normally do C#, so naturally anything in VB scares, but I think the LINQ syntax should be similar

Answer 3

You could use full text search and still use LINQ to SQL. You can create a stored procedure and have LINQ call it. In addition to finding exact matches you could return:

• Simple searches for specific words or phrases
• Thesaurus searches for synonymous forms of word – a search on IE might return hits to Internet Explorer and IE (thesaurus based expansion search); a search on Bombay might also return hits to Mumbai (thesaurus based replacement search)
• Searches that will return all different linguistic forms of a word (called generations) search on bank would return hits to banking, banked, banks, banks' and bank's, etc. (all declensions and/or conjugations of the search term bank)
• Accent insensitive searches – a search on café would return hits to cafe and café

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/