Full Text Search using SQL Server 2008, C# and Entity Framework

So, there I was trying to add fulltext searching to my application that’s written using ASP.NET C#, MVC3, Entity Framework 4 and Sql Server 2008 R2.  I created the required catalog and fulltext indexes on my table. And then got a bit stuck finding the best way to execute the fulltext search itself from with my application, due to the fact that Entity Framework doesn’t support this out-of-the box. 

After a bit of googling, trying different options and testing, I found a workaround to this problem.  This post covers my workaround solution.  Hopefully this will help somebody who’s trying to do something similar.  Please note, the explanation is centered around Sql Server 2008 R2 and EF version 4.

Fulltext searches let users run full text queries against database columns that are character based. So these can be run against char, nchar, varchar, nvarchar, text and ntext. They can also be run against documents and/or image files, so columns of type image, xml and varbinary(max) can also be used to run fulltext queries. 

To start using the fulltext search capability, we first have to create a fulltext catalog in the database followed by a fulltext index on the underlying column(s) of the table we are searching.  Once this is done, you can use one of the two predicates – CONTAINS or FREETEXT – to perform your search. You can also use two of the fulltext functions – CONTAINSTABLE and FREETEXTTABLE.  Full details of these predicates and functions can be found in the MSDN library.

I had one other requirement which was that I wanted to use stop words, so we weren’t querying on commonly used words like ‘a’, ‘an’, ‘for’, etc.  This is quite similar to what google does.  SQL Server ships with a default stoplist called SYSTEM. 

From the MSDN library

‘To prevent a full-text index from becoming bloated, SQL Server has a mechanism that discards commonly occurring strings that do not help the search. These discarded strings are called stopwords. During index creation, the Full-Text Engine omits stopwords from the full-text index. This means that full-text queries will not search on stopwords.’

For our purposes, I am just going to use the SYSTEM stoplist that is provided with Sql Server. Though you can create your own stoplists if required. 

So, let’s get down to some coding. 

First, I create the fulltext catalog as follows. A catalog is a logical structure in Sql Server and is used to group fulltext indexes.  Each fulltext index lives within a catalog and you can have one or more fulltext indexes in one catalog.  To create the catalog, I use the following statement:  

CREATE FULLTEXT CATALOG  my_fulltext_cat AS DEFAULT;

So now I have a new catalog called ‘my_fulltext_cat’. This catalog has been created as DEFAULT, which means that if I do not explicitly specify the catalog to use, this catalog will be used.

Next, we are going to create our fulltext index.  Before you can do this, you have to ensure that you have a unique indexed key on the table you are created the fulltext index on.  This key should be based on a single non-nullable column and is used by the fulltext engine to map each row to the unique key.

CREATE  UNIQUE INDEX product_id_UK ON dbo.t_product(ID);

CREATE FULLTEXT INDEX ON dbo.t_product (prod_name LANGUAGE 1033, prod_desc LANGUAGE 1033) KEY INDEX product_id_UK ON my_fulltext_cat WITH STOPLIST = SYSTEM;

OK, that’s a long statement there! What it does is create a fulltext index on the table t_product.  This is based on two columns – prod_name and prod_desc – and in the english language (1033 is the language code for english). It uses the unique index we have on product_id and is part of the my_fulltext_cat catalogue.  Remember how I said I wanted to use stop words?  Well the line ‘WITH STOPLIST = SYSTEM’ basically says to use the stop list SYSTEM, to take out commonly used terms that won’t help my query. 

Now, once you have created the above, you can easily run the following query in Sql Server to match words to the prod_name and prod_desc columns as follows:

SELECT * FROM t_product WHERE FREETEXT(prod_name, prod_desc), ‘product1 product2 product3’);

The above statement displays all rows in which either product name or description matches ANY OF THE WORDS in the search string.  If instead you wanted to match ALL OF THE WORDS, you can use the CONTAINS predicate instead. 

OK, so far so good.  Now the part where you try and get this done in Entity Framework. And come undone!  Entity Framework does not (currently) have support for fulltext predicates. The workaround in this case is to pass the Select query to the database directly using ‘ExecuteStoreQuery’ on the context object and then manipulating the resultset sent back. 

So my C# code looks like this. Since I’m using MVC3, this goes into a controller method that returns a JsonResult.  Please note, that I have to convert the DbContext object to the ObjectContext to be able to call ExecuteStoreQuery on it. 

[HttpPost]

public JsonResult SearchProd(string str)

{

object[] parameters ={ str };

 var query = “SELECT ID, prod_name FROM t_product WHERE FREETEXT((prod_name, prod_desc), {0})”;

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

       .ObjectContext.ExecuteStoreQuery<Product>(query, str);

  return Json(result);

 }

And there you have it.  FullText search enabled in your application. 

P.S: If you want to use the fulltext related functions – CONTAINSTABLE and FREETEXTTABLE – instead with Entity Framework, you can create a table-valued function that calls the above functions and returns the result set as required.  Perhaps I will cover how to do this in a later post some day.

P.P.S: You can also just use DbContext and SqlQuery method in the same way as above, without using ObjectContext and ExecuteStoreQuery.