Entity Framework and Full Text Search

Out of the box Entity Framework does not support Full Text Search. To do Full Text Search you have a number of options to get it working. In this blog post I’ll describe a method to get Full Text Search working using a table valued function. The method does the Full Text Search in a table valued function that returns an id list, the returned ids can be used to filter the records from an actual table.

The solution I use to get Full Text Search working with Entity Framework uses the following techniques/packages:

  • Entity Framework 6.1.2
  • Entity Framework Functions 1.3.1.0
  • Sql table valued functions
  • Entity Framework interception

Use Full Text Search from code
After you have implemented the infrastructure to use Full Text Search from Entity Framework, the code that uses the Full Text Search can look like this:

Under laying Table Valued Function

Get started with Full Text Search will give you some context on how to setup full text search. You need to prepare your table/column to be searchable.

The goal is to search the TextField column in the Sample table on the words in the searchtxt. The Full Text Search is done in a table valued function called fnSampleTableFTS. This function gets the searchterm parameter. The result of the function is a list of ids.

Database context

EntityFramework.Functions library provides a few simple APIs, following the pattern of Entity Framework and LINQ to SQL.

To connect the function call to the table valued function we need to implement a custom datacontext which enables you to connect the function in the database to a function on your database context. The mapping is done with Entity Framework Functions. In the following sample datacontext, the function fnSampleTableFTS is mapped to the fnSampleTableFTS table valued function in the database. In the function, the parameters are prepared and sanitized (AndPartsFormatter) so they can be used as Full Text Search parameters. In the OnModelCreating the function and new type are registered on the context.

Formatting the search term
To make Full Text Search work, you need to format the search term. You can choose on how to search in to column by formatting the search term. In this case I split the words and add AND to find match on all words. Besides preparing the input I sanitize the input to prevent nasty stuff inserted into my search terms. More options on how to format your search term or setup your query: query with Full Text Search.

Correct the length of the search parameter
As last you need to add an IDBCommandInterceptor to change the length of your query parameter (it is registered in the OnModelCreating method in the context). Full Text Search only accepts nvarchar sizes till 1000. Entity Framework makes all parameters standard 4000. To correct this the interceptor replaces the size of your search parameter with length 1000:

When everything is in place you can easily change the behavior of your search by updating the table valued function or change the formatting of the search term.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s