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:

var items = _context.Samples.Where(sample =>
                _context.fnSampleTableFTS(searchtxt)
                    .Select(s => s.ID)
                    .Contains(sample.ID))
                .ToList();

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.

CREATE FUNCTION [dbo].[fnSampleTableFTS]
(
	@searchterm nvarchar(1000)
)
RETURNS TABLE
AS
RETURN
(
   SELECT SampleTable.Id
     FROM dbo.SampleTable
    WHERE Contains(SampleTable.TextField, @searchterm)
)

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.

public class FtsContext : DbContext
{
    public FtsContext(string connectionString) : base(connectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Add functions to entity model.
        modelBuilder.Conventions.Add(new FunctionConvention());
        modelBuilder.AddComplexTypesFromAssembly(typeof(SampleIds).Assembly);

        DbInterception.Add(new SearchTermInterceptor());
    }

    public DbSet Samples { get; set; }

    [Function(FunctionType.TableValuedFunction, nameof(fnSampleTableFTS), "dummyNamespace", Schema = dbo)]
    public IQueryable fnSampleTableFTS([Parameter(DbType = "nvarchar")]string searchterm)
    {
        ObjectParameter searchtermParameter;
        if (string.IsNullOrWhiteSpace(searchterm))
            searchtermParameter = new ObjectParameter(SearchTermInterceptor.SEARCHTERMNAME, typeof(string));
        else
            searchtermParameter = new ObjectParameter(SearchTermInterceptor.SEARCHTERMNAME, String.Format("{0}", searchterm.AndPartsFormatter(new object())));

        return this.ObjectContext().CreateQuery(
            $"[{nameof(this.fnSampleTableFTS)}](@{SearchTermInterceptor.SEARCHTERMNAME})", searchtermParameter);
    }
}

[ComplexType]
public class SampleIds{
    public int Id{ get; set; }
}

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.

public static class SearchTermFormatExtentions
{
    private static string SanitizeSearchTerm(string searchPhrase)
    {
        if (searchPhrase.Length > 200)
            searchPhrase = searchPhrase.Substring(0, 200);
        searchPhrase = searchPhrase.Replace(";", " ");
        searchPhrase = searchPhrase.Replace("'", " ");
        searchPhrase = searchPhrase.Replace("--", " ");
        searchPhrase = searchPhrase.Replace("/*", " ");
        searchPhrase = searchPhrase.Replace("*/", " ");
        searchPhrase = searchPhrase.Replace("xp_", " ");
        return searchPhrase;
    }
    public static string AndPartsFormatter(this string searchterm, object t)
    {
        var searchterms = SanitizeSearchTerm(searchterm).Split(' ');
        searchterms = searchterms.Select(s => "\"" + new string(s.Where(c => char.IsLetterOrDigit(c)).ToArray()) + "*\"").ToArray();
        searchterm = string.Join(" AND ", searchterms);
        return searchterm;
    }
}
Model

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:

public class SearchTermInterceptor : IDbCommandInterceptor
{
    public const string SEARCHTERMNAME = "fulltextsearchparam";
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
    {
    }
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext interceptionContext)
    {
    }
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
    {
        RewriteSearchTerm(command);
    }
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext interceptionContext)
    {
    }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
    {
        RewriteSearchTerm(command);
    }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext interceptionContext)
    {
    }

    public static void RewriteSearchTerm(DbCommand cmd)
    {
        string text = cmd.CommandText;
        for (int i = 0; i < cmd.Parameters.Count; i++)
        {
            DbParameter parameter = cmd.Parameters[i];
            if (parameter.ParameterName == SEARCHTERMNAME)
            {
                parameter.Size = 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.

Advertisement