Use Full-Text Search in SQL Server 2008 with Entity Framework

This is my first article about Full-Text search in SQL Server 2008. I had a task connected with discovering how can we implement 'google-like' search in our desktop solution. We use Entity Framework 6.0 in our project and the main aim I tried to reach was to find full-text search engine compatible with EF. I found this engine and wrote article about this engine: "Используем Lucene.Net для высокоточного полнотекстового поиска" in Russian. However, I also want to try using Full-Text Search engine in SQL Server 2008 with EF. In addition, this article is just about this. As a bonus in the end of the article, I will compare FTS in SQL Server with my choice Lucene.Net. I hope this article will be helpful to embed FTS in SQL Server in your applications. Let's see the full circle from creating catalog in database to implementing some logic for searching combined with EF.

1. The first step is to create a new catalog for saving index.

2. The second step is to create a new catalog.

3.  See catalog in DB:

4.  Create index for some table 'Define Full-Text Index…'

5.  Welcome page:

6. Select unique index.

7.  Select available column for index.

8.    Select change tracking.

9.    Select catalog, stop list of words and filegroup index.

        10.  Define population schedulers.

In this window you can create scheduler for updating index.

11. Final wizard page:

12. Create index operation performed.

13. Start Full Population for selected table for index created before.

14. Result of population:

15.  Using Full Text search from SQL Management Studio:

16. Using search in EF:

public IQueryable<Order> SearchData(string search)

{

    try

    {

        object[] parameters = { search };

            var query = "SELECT * FROM [OrderCustomFields] where FREETEXT(*, '{0}')";

            var result = (Db as System.Data.Entity.Infrastructure.IObjectContextAdapter)

                .ObjectContext.ExecuteStoreQuery<Order>(query, search);

Full documentation you can find in MSDN. A big problem with using this way is caused by a lot of words with direct selection data from database and manual query for search data.

The main problem I encountered with was the FTS in MS SQL Server that could not create searching index for multiple table. Only one table – one index. It’s not actual problem for Lucene.Net. Lucene can create complex search index with multiple tables for search. MS FTS is a part of SQL Server so you will have to write SQL queries like CONTAINS, FULLTEXT, etc. Lucene.Net provides a special API for solving the same issue. Both of them support work with stop-words, synonyms, stemming and multiple columns querying.  MS FTS support automation recreate and update index, but Lucene.Net does not support this functionality.

Conclusion

This article is not about how to choose Lucene.Net or MS FTS. It is supposed to show you how to implement FTS for vast amount of text data in MS SQL Server. If your search engine requirements are not so strong, maybe using SQL Server Full Text Search will be the best option – it is very simple to maintain and fast enough with simple queries. On the other hand, if you need more complicated search engine for searching in multiple documents or creation different queries with powerful API, I think the best choice for you is to search engine like Lucene.Net or Solr. Or if money is not a big problem for you and if you prefere using private search engine (not open-source), so the best choice could be Sphinx Full-Text Search Engine because its API is similar to EF syntax.