c# - A "Composable" Full Text Search with a Code First Model

Question

UPDATE 18 Sep 2013

It looks like there isn't an easy way to do this. I'm holding out for a solution that involves some extension to Entity Framework.

If you'd like to see these features in Entity Framework, vote for them on the user voice site, perhaps here and here

---

There are several similar questions on SO but I can't find a question new and similar enough to have the answer I'm looking for.

If this looks like information overload, jump down to In Summary.

Background

I'm writing a WebApi REST service to expose some pre-existing data through an OData end point. I'm using the EntitySetContoller<TEntity, TKey> to do all the grunt work for me. As well as the standard OData parameters, that are routed and translated by the base class, I've added some custom parameters, to allow specific functionality for my controller.

My database server is MS SQL Server with a full text index on the [BigText] NVarChar[4000] column of the [SomeEntity] table.

I have one limitation, I must use a Code First model.

// Model POCO
public class SomeEntity
{
    public int Id { get; set; }
    public string BigText { get; set; }
}

// Simple Controller
public class SomeEntityController : EntitySetController<SomeEntity, int>
{
    private readonly SomeDbContext context = new SomeDbContext();

    public override IQueryable<SomeEntity> Get()
    {
        var parameters = Request.GetQueryNameValuePairs()
            .ToDictionary(p => p.Key, p => p.Value);

        if (parameters.ContainsKey("BigTextContains")
        (
            var searchTerms = parameters["BigTextContains"];
            // return something special ... 
        )

        return this.context.SomeEntities;
    }

    // ... The rest is omitted for brevity.
}

The Problem

How to implement the // return something special ... part of my example?

Obviously, the niave

return this.context.SomeEntities.Where(e =>
    e.BigText.Contains(searchTerm));

is completely wrong, it composes to a WHERE clause like

[BigText] LIKE '%' + @searchTerm + '%'

This doesn't use Full Text Searching so, doesn't support complex search terms and otherwise, performs terribley.

This approach,

return this.context.SomeEntities.SqlQuery(
    "SELECT E.* FROM [dbo].[SomeEntity] E " +
        "JOIN CONTAINSTABLE([SomeEntity], [BigText], @searchTerm) FTS " +
            " ON FTS.[Key] = E.[Id]",
    new object[] { new SqlParameter("@searchTerm", searchTerm) })
    .AsQueryable();

Looks promising, it actually uses Full Text Searching, and is quite functional. However, you'll note that DbSqlQuery, the type returned from the SqlQuery function does not implement IQueryable. Here, it is coerced to the right return type with the AsQueryable() extension but, this breaks the "chain of composition". The only statement that will be performed on the server is the one specified in the code above. Any additional clauses, specified on the OData URL will be serviced on the API hosting web server, without benefitting from the indices and specialised set based functionality of the database engine.

In Summary

What is the most expedient way of accessing MS SQL Server's Full Text Search CONTAINSTABLE function with an Entity Framework 5 Code First model and acquiring a "composable" result?

Do I need to write my own IQueryProvider? Can I extend EF in some way?

I don't want to use Lucene.Net, I don't want to use a Database Generated Model. Perhaps I could add extra packages or wait for EF6, would that help?

Answer 1

It is not perfect, but you can accomplish what you are after with 2 calls to the database. The first call would retrieve a list of matching key's from CONTAINSTABLE and then the second call would be your composable query utilizing the IDs that you returned from the first call.

//Get the Keys from the FTS
var ids = context.Database.SqlQuery<int>( 
          "Select [KEY] from CONTAINSTABLE([SomeEntity], [BigText], @searchTerm)", 
          new object[] { new SqlParameter("@searchTerm", searchTerm) });

//Use the IDs as an initial filter on the query
var composablequery = context.SomeEntities.Where(d => ids.Contains(d.Id));

//add on whatever other parameters were captured to the 'composablequery' variable
composablequery = composablequery.Where(.....)