Freetext Extension in Entity Framework Code First

I posted before a solution for adding custom SQL functions to Entity Framework Code First as extension methods. This time I am going to show how we can do something similar for the FREETEXT function of SQL Server. Please note that this example will only work if you have the Fulltext Search component installed and your table is indexed.

OK, so we want to have an extension method like this:

[DbFunction("CodeFirstDatabaseSchema", "FREETEXT")]
public static Boolean Freetext(this String column, String value)
    return column.Contains(value);

In order for Entity Framework to recognize it, we need to write our own convention, this is because Entity Framework only recognizes out of the box a number of SQL Server built-in functions. We can write one as this:

public class FreetextConvention : IStoreModelConvention<EdmModel>
    public static readonly FreetextConvention Instance = new FreetextConvention();
    public void Apply(EdmModel item, DbModel model)
        var valueParameter = FunctionParameter.Create("column", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
        var formatParameter = FunctionParameter.Create("value", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
        var returnValue = FunctionParameter.Create("result", this.GetStorePrimitiveType(model, PrimitiveTypeKind.Boolean), ParameterMode.ReturnValue);
        var function = this.CreateAndAddFunction(item, "FREETEXT", new[] { valueParameter, formatParameter }, new[] { returnValue });
    protected EdmFunction CreateAndAddFunction(EdmModel item, String name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues)
        var payload = new EdmFunctionPayload { StoreFunctionName = name, Parameters = parameters, ReturnParameters = returnValues, Schema = this.GetDefaultSchema(item), IsBuiltIn = true };
        var function = EdmFunction.Create(name, this.GetDefaultNamespace(item), item.DataSpace, payload, null);
        return (function);
    protected EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
        return (model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType);
    protected String GetDefaultNamespace(EdmModel layerModel)
        return (layerModel.GlobalItems.OfType<EdmType>().Select(t => t.NamespaceName).Distinct().Single());
    protected String GetDefaultSchema(EdmModel layerModel)
        return (layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault());

This registers a FREETEXT function with two string parameters and returning a boolean. All is fine, we add it to the DbContext in OnModelCreating:


You might have noticed the usage of a Instance static field, this is because, since the FreetextConvention class is stateless, there’s no point in creating many of them, we can just use the same instance.

Now, if we issue a LINQ query as:

var customers = ctx.Customers.Where(x => x.Name.Freetext("ricardo")).ToList();

It will fail miserably, complaining about this SQL fragment:

WHERE ((FREETEXT(name, N'ricardo') = 1)

The “= 1” part is here because the function is prototyped as boolean, which maps to SQL Server’s BIT data type, and the value for true is 1. Apparently, SQL Server does not support comparisons of some functions with 1; but if we run it as:

WHERE ((FREETEXT(name, N'ricardo'))

without the explicit comparison, it works perfectly. So, all we have to do is get rid of “= 1”. Fortunately, Entity Framework, as of version 6, offers some very nice extensibility points. There are at least two ways by which we can achieve this:

  • By intercepting the command tree;
  • By intercepting the raw SQL.

Here we will use option #2 and leave command trees for another post.

We need to identity something with a format of “FREETEXT(something) = 1”. We can do it using a regular expression, and the interception of the SQL command can be achieved by implementing IDbCommandInterceptor (no reference documentation yet, but I have reported it and it will soon be fixed, hopefully) and registering one such instance in the DbInterception (same) static class. An IDbCommandInterceptor implementation might look like this:

public class FreetextInterceptor : IDbCommandInterceptor
    public static readonly FreetextInterceptor Instance = new FreetextInterceptor();
    private static readonly Regex FreetextRegex = new Regex(@"FREETEXT\(([^)]+\))\) = 1");
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        var matches = FreetextRegex.Matches(command.CommandText);
        if (matches.Count > 0)
            command.CommandText = FreetextRegex.Replace(command.CommandText, "FREETEXT($1)");
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<Object> interceptionContext)
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<Object> interceptionContext)

You can see that the only method we’re interested in is ReaderExecuting (again, no documentation available), with is the one that will be called just before a SQL SELECT query is sent to the database. In here we analyze the CommandText property of the DbCommand and get rid of the “= 1” clause, using a regular expression. Finally, we need to register the interceptor before we issue the query, maybe in the static constructor of our DbContext:


And now we can finally execute our query:

var customers = ctx.Customers.Where(x => x.Name.Freetext("ricardo")).ToList();

And that’s it. Don’t forget that in order for this to work, you need to enable Full Text Search.



  • Wow very nice post, Should the function be really prototyped as bool, why not string ??? ( would really help if you could help me on this), secondly there is little help on command trees, would be very helpful for all devs if you drive usecases out of command trees, many times, i try to use them, always run into fatal error.


  • Hi, Raghav! Thanks!
    It should be prototyped as bool, because otherwise you couldn't use it in a Where LINQ restriction:

    ctx.Customers.Where(x => x.Name.Freetext("bla"));

    Also, the SQL Function is also bool, for the same reason:

    WHERE FREETEXT('name', 'bla')

    These are conditions, which must always be boolean.
    As for command trees, yes, I'm planning on writing a post on them; one common usage is implementing soft deletes; see and

  • Hi Ricardo, looks like there's a small typo...the second code block that says "WHERE ((FREETEXT(name, N'ricardo') = 1)" still has the "= 1" part in it.

    Cheers and thanks as always for your excellent blog!

  • Hi, Brian!
    Thanks a lot! Updated now!

  • Hi Ricardo, I applied your solution to a project with I'm working with it compiles but I keep getting the following Exception:

    System.InvalidOperationException: La secuencia contiene más de un elemento

    the exception is thrown at:

    protected String GetDefaultSchema(EdmModel layerModel)
    return (layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault());

    any thoughts?

  • Hi, Rodrigo! I'll have a look tomorrow and will let you know.

  • Or, better: can you send me your project (just enough to see the problem)?
    My email is rjperes at hotmail.

  • Hi Ricardo, I managed to make it work by replacing

    return (layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault());


    return (layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().FirstOrDefault());

    The thing is that now my queries are not matching the RegEx pattern and every query with a FREETEXT ends up throwing an exception.

    This is one of the queries I'm using:

    [Project1].[Id] AS [Id],
    [Project1].[Nombre] AS [Nombre],
    [Project1].[ItemUnico] AS [ItemUnico],
    [Project1].[Stock] AS [Stock],
    [Project1].[NroIdentificacion] AS [NroIdentificacion],
    [Project1].[FechaAdquisicion] AS [FechaAdquisicion],
    [Project1].[FechaUso] AS [FechaUso],
    [Project1].[Costo] AS [Costo],
    [Project1].[FechaBaja] AS [FechaBaja],
    [Project1].[MotivoBaja] AS [MotivoBaja],
    [Project1].[SubtipoRecursoId] AS [SubtipoRecursoId],
    [Project1].[EstadoId] AS [EstadoId],
    [Project1].[NodoEstructuraId] AS [NodoEstructuraId],
    [Project1].[ResponsableCreacionId] AS [ResponsableCreacionId],
    [Project1].[CreatedDate] AS [CreatedDate],
    [Project1].[ModifiedDate] AS [ModifiedDate],
    [Project1].[Deleted] AS [Deleted],
    [Project1].[DeleteDate] AS [DeleteDate]
    [Extent1].[Id] AS [Id],
    [Extent1].[Nombre] AS [Nombre],
    [Extent1].[ItemUnico] AS [ItemUnico],
    [Extent1].[Stock] AS [Stock],
    [Extent1].[NroIdentificacion] AS [NroIdentificacion],
    [Extent1].[FechaAdquisicion] AS [FechaAdquisicion],
    [Extent1].[FechaUso] AS [FechaUso],
    [Extent1].[Costo] AS [Costo],
    [Extent1].[FechaBaja] AS [FechaBaja],
    [Extent1].[MotivoBaja] AS [MotivoBaja],
    [Extent1].[SubtipoRecursoId] AS [SubtipoRecursoId],
    [Extent1].[EstadoId] AS [EstadoId],
    [Extent1].[NodoEstructuraId] AS [NodoEstructuraId],
    [Extent1].[ResponsableCreacionId] AS [ResponsableCreacionId],
    [Extent1].[CreatedDate] AS [CreatedDate],
    [Extent1].[ModifiedDate] AS [ModifiedDate],
    [Extent1].[Deleted] AS [Deleted],
    [Extent1].[DeleteDate] AS [DeleteDate]
    FROM [AdmRec].[Recursos] AS [Extent1]
    WHERE (FREETEXT(LOWER([Extent1].[Nombre]), LOWER(@p__linq__0))) = 1
    ) AS [Project1]
    ORDER BY [Project1].[Nombre] ASC

Add a Comment

As it will appear on the website

Not displayed

Your website