Search Database - ASP.NET MVC C#

Question

I'm attempting to implement complete search functionality in my ASP.NET MVC (C#, Linq-to-Sql) website.

The site consists of about 3-4 tables that have about 1-2 columns that I want to search.

This is what I have so far:

    public List<SearchResult> Search(string Keywords)
    {
        string[] split = Keywords.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
        List<SearchResult> ret = new List<SearchResult>();
        foreach (string s in split)
        {
            IEnumerable<BlogPost> results = db.BlogPosts.Where(x => x.Text.Contains(s) || x.Title.Contains(s));

            foreach (BlogPost p in results)
            {
                if (ret.Exists(x => x.PostID == p.PostID))
                    continue;

                ret.Add(new SearchResult
                {
                    PostTitle= p.Title,
                    BlogPostID = p.BlogPostID,
                    Text=p.Text
                });

            }
        }
        return ret;
    }

As you can see, I have a foreach for the keywords and an inner foreach that runs over a table (I would repeat it for each table).

This seems inefficent and I wanted to know if theres a better way to create a search method for a database.

Also, what can I do to the columns in the database so that they can be searched faster? I read something about indexing them, is that just the "Full-text indexing" True/False field I see in SQL Management Studio?

Answer 1

The following should do the trick. I can't say off the top of my head whether the let kwa = ... part will actually work or not, but something similar will be required to make the array of keywords available within the context of SQL Server. I haven't used LINQ to SQL for a while (I've been using LINQ to Entities 4.0 and nHibernate for some time now, which have a different set of capabilities). You might need to tweak that part to get it working, but the general principal is sound:

public List<SearchResult> Search(string keywords)
{    
  var searcResults = from bp in db.BlogPosts
                     let kwa = keywords.Split(new char[]{' '}, StringSplitOptions.RemoveEmptyEntries);
                     where kwa.Any(kw => bp.Text.Contains(kw) || bp.Title.Contains(kw))
                     select new SearchResult
                     {
                        PostTitle = bp.Title,
                        BlogPostID = bp.BlogPostID,
                        Test = bp.Text
                     };

  return searchResults.ToList();
}

Answer 2

Also, what can I do to the columns in the database so that they can be searched faster? I read something about indexing them, is that just the "Full-text indexing" True/False field I see in SQL Management Studio?

Yes, enabling full-text indexing will normally go a long way towards improving performance for this scenario. But unfortunately it doesn't work automatically with the LIKE operator (and that's what your LINQ query is generating). So you'll have to use one of the built-in full-text searching functions like FREETEXT, FREETEXTTABLE, CONTAINS, or CONTAINSTABLE.

Just to explain, your original code will be substantially slower than full-text searching as it will typically result in a table scan. For example, if you're searching a varchar field named title with LIKE '%ABC%' then there's no choice but for SQL to scan every single record to see if it contains those characters.

However, the built-in full-text searching will actually index the text of every column you specify to include in the full-text index. And it's that index that drastically speeds up your queries.

Not only that, but full-text searching provides some cool features that the LIKE operator can't give you. It's not as sophisticated as Google, but it has the ability to search for alternate versions of a root word. But one of my favorite features is the ranking functionality where it can return an extra value to indicate relevance which you can then use to sort your results. To use that look into the FREETEXTTABLE or CONTAINSTABLE functions.

Some more resources:

• Full-Text Search (SQL Server)
• Pro Full-Text Search in SQL Server 2008