Search database - ASP.NET MVC C #
I am trying to implement full search functionality in my ASP.NET MVC (C #, Linq-to-Sql) website.
The site consists of about 3-4 tables containing about 1-2 columns which I want to execute.
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 keywords and an internal foreach that runs over the table (I would repeat it for each table).
This seems to be inefficient and I wanted to know if the best way to create a search method for the database.
Also, what can I do for the columns in the database so that they can be searched faster? I read something about indexing them, is it just "full text indexing" of the True / False field that I see in SQL Management Studio?
source to share
Also, what can I do for columns in the database so that they can be searched faster? I read something about indexing them, is that only "Full Text Indexing" Field is True / False I see in SQL Management Studio?
Yes, enabling full-text indexing will generally significantly improve performance for this scenario. But unfortunately it doesn't work automatically with the LIKE operator (and whatever generates your LINQ query). Thus, you will have to use one of the built-in full-text search functions such as FREETEXT, FREETEXTTABLE, CONTAINS, or CONTAINSTABLE.
Just for the sake of explanation, your source code will be significantly slower than full-text search as it usually results in table scans. For example, if you are looking for a varchar field named title with LIKE '% ABC%' then there is no choice other than SQL to check each record to see if it contains those characters.
However, the built-in full-text search actually indexes the text of each column that you specify to be included in the full-text index. And this is the index that dramatically speeds up your queries.
Not only that, but full-text search provides some interesting functionality that the LIKE operator cannot give you. It's not as hard as Google, but it does have the ability to search for alternative versions of the root word. But one of my favorite features is the ranking function, where it can return an additional value to indicate relevance, which you can then use to sort the results. To use this view in the FREETEXTTABLE or CONTAINSTABLE function.
A few additional resources:
source to share
The following should do the trick. I can't tell if the let kwa = ... part will work or not, but something like this would be required to make the keyword array available in 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 a while now, which have a different set of capabilities). You may need to tweak this part to make it work, but the main principle is:
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();
}
source to share