Entity Framework and Full Text Search

Image with someone in the background

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
  • 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.


7 thoughts on “Entity Framework and Full Text Search”

  1. in the method SanitizeSearchTerm is there a specific reason that you restrict the search term to 200 characters?


  2. Some of this code doesn’t seem to work.
    Schema = dbo – there’s no reference to dbo. I’ve changes this to Schema = “dbo”
    It returns an IQueryable but makes use of an ObjectContext – doesn’t this mean it will call out to the database first, return the results of the function then use those results in your linq Contains query?
    CreateQuery expects to use a generic version, there’s no non-generic version available.


  3. One more odd thing I found is in your initial example of how you would use the function – _context.fnSampleTableFTS(searchtxt).Select(s => s.ID)
    Select in this case doesn’t accept Funcs or Expressions, only strings as the first parameter.

    I’m using .net 4.5, Entity Framework 6.2 and Entity Framework Functions 1.4.1.


  4. Somehow my previous comment just went missing. Another odd thing I found is that the Select method in your initial example doesn’t accept predicates (Funcs or Expressions), only a string.


    1. Thank you for your comments. It seems that you have probably a different version of the EF Functions or EF as I used a the time. Rewriting the code to a generic version would probably not change much. However I do not have access to the original code the sample was based on, so I cannot test/see if it fails on newer versions that I used. I would recommend to use the sample code as a guide to implement fts.


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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: