"Compiled" full-text search with the first code model

Question

UPDATE September 18, 2013

There seems to be no easy way to do this. I support a solution that involves some extension to the Entity Framework.

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

---

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

If this sounds like information overload, go to the CV summary .

Background

I am writing a WebApi REST service to expose some pre-existing data through an OData endpoint. I am using EntitySetContoller<TEntity, TKey> to do all the work for me. In addition to the standard OData parameters that are routed and translated by the base class, I added some custom parameters to provide some functionality for my controller.

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

I have one limitation, I have to use the 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. } 

Problem

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

Obviously niave

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

completely incorrect, it consists of a WHERE type

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

This does not use full-text search, therefore it does not support complex search queries and, conversely, it performs horribly.

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(); 

It looks promising, actually uses full-text search and is quite functional. However, you will notice that DbSqlQuery , the type returned by the SqlQuery function, does not implement IQueryable . Here it is forced to the correct return type with the AsQueryable() extension, but this breaks the "composition chain." The only statement that will be executed on the server is the one specified in the code above. Any additional offers specified in the OData URL will be served on a web server that supports the API, without the use of indexes and specialized database-based functions.

In summary

What is the most appropriate way to access full-text search in MS SQL Server CONTAINSTABLE using the Entity Framework 5 Code First model and acquire a “composite” result?

Do I need to write my own IQueryProvider ? Is there any way to expand EF?

I do not want to use Lucene.Net, I do not want to use the created database. Maybe I could add extra packages or wait for EF6, will this help?

Answer 1

This is not ideal, but you can accomplish what you need with two calls to the database. The first call will retrieve a list of matching keys from CONTAINSTABLE, and then the second call will be your composite request using the identifiers 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(.....) 

Answer 2

I had the same issue recently: EF 5 Code First FTS Queriable

Let me extend this post.

• Your first option was my first, using SqlQuery I also needed to do more filtering, so instead of writing the full sql file, I used QueryBuilder, to which I made some changes and added more functions to suit my needs (I could download it somewhere if necessary): QueryBuilder

• After I found another idea that I implemented. Someone already mentions this here, that is, use SqlQuery, which will return a HashSet of identifiers and that you can use it in EF queries using Contains. This is better, but not the most optimal, since you need 2 queries and a list of identifiers in memory. Example:

    public IQueryable<Company> FullTextSearchCompaniesByName(int limit, int offset, string input, Guid accountingBureauId, string orderByColumn) { FtsQueryBuilder ftsQueryBuilder = new FtsQueryBuilder(); ftsQueryBuilder.Input = FtsQueryBuilder.FormatQuery(input); ftsQueryBuilder.TableName = FtsQueryBuilder.GetTableName<Company>(); ftsQueryBuilder.OrderByTable = ftsQueryBuilder.TableName; ftsQueryBuilder.OrderByColumn = orderByColumn; ftsQueryBuilder.Columns.Add("CompanyId"); if (accountingBureauId != null && accountingBureauId != Guid.Empty) ftsQueryBuilder.AddConditionQuery<Guid>(Condition.And, "" , @"dbo.""Company"".""AccountingBureauId""", Operator.Equals, accountingBureauId, "AccountingBureauId", ""); ftsQueryBuilder.AddConditionQuery<bool>(Condition.And, "", @"dbo.""Company"".""Deleted""", Operator.Equals, false, "Deleted", ""); var companiesQuery = ftsQueryBuilder.BuildAndExecuteFtsQuery<Guid>(Context, limit, offset, "Name"); TotalCountQuery = ftsQueryBuilder.Total; HashSet<Guid> companiesIdSet = new HashSet<Guid>(companiesQuery); var q = Query().Where(a => companiesIdSet.Contains(a.CompanyId)); return q; } 

• However, there is now something called Interceptors in EF 6 that can be used to implement Queriable FTS, and it's pretty simple and general (last post): EF 6 interceptors for FTS . I tested this and it works great.

!! NOTE: EF code. First of all, even with version 6, it does not support user-defined stored procedures. There are only some of the predefined CUD operations, if I understand them well: Code First Insert / Update / Delete the stored matching procedure , so it cannot be done with it.

Conclusion: if you can use EF 6 for the third options, this gives you everything you need. If you are stuck with EF 5 or less, the second option is better than the first, but not the most optimal.