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?

You can define a stored procedure & then you can call with regular lQueryable — Akash Kava, Sep 14 '13 at 08:42
@AkashKava, can you? Please show me how to call a SP and return a "composable" `IQueryable`. — Jodrell, Sep 16 '13 at 08:27
Checkout last paragraph/section on this linq, it is little complicated, but if it works for you, I will put up as answer, http://www.scip.be/index.php?Page=ArticlesNET35 — Akash Kava, Sep 16 '13 at 09:38
@AkashKava, thats database first with EF4.0. Unless I'm mistaken. — Jodrell, Sep 16 '13 at 09:49
You can still use same techniques with Code first also, querying and LINQ is exactly same in both. — Akash Kava, Sep 16 '13 at 10:39
Unfortunately it doesn't look promising, it appears to use full text searching and LINQ you will need to define your own function. (see: http://bit.ly/150q12u) These can obviously be easily mapped to your object meaning the Code First technique is still being implemented. — talegna, Sep 16 '13 at 15:50
@telenga, so how could I call the function and return a "composable" `IQueryable`? — Jodrell, Sep 16 '13 at 16:23
Have you tried using an ObjectQuery? ObjectQuery query = context.CreateQuery(@"[query]"); — cbeckner, Sep 17 '13 at 09:14
I've not replicated the environment you describe above yet. I'll do so in a few hours, try my own advice and let you know. — cbeckner, Sep 17 '13 at 11:34
From what I could tell after reading this article (http://msdn.microsoft.com/en-us/magazine/hh394151.aspx) it looks as though this is possible if you wrap the `CONTAINSTABLE` functionality in a table-value function (which can return `IQueryable`) and then access it via EF. I'm not sure I completely understand the way the selections work in `CONTAINSTABLE`, so I'm hesitant to write a full answer. Hope it helps. — devstruck, Sep 19 '13 at 20:52
@post_erasmus, that article uses a database first approach. Which poses the question, if you can do it database first, why not code first? Well, I've tried, I can't find a way to make a composable call to a TVF. — Jodrell, Sep 20 '13 at 08:19
@Jodrell While I recognize the difference, I had hoped you would be able to use that as a point of reference for Code First. Upon a further search, I cannot find any non-question result for Code-First Table-Value Functions in SO, Google, or even Bing that's truly connected. Good luck. — devstruck, Sep 20 '13 at 17:08
I have posted some hack at http://stackoverflow.com/a/19644900/335784. Idea is to add magic world at the search text beginning and then hook and modify SQL in DbInterceptor — Ben, Oct 28 '13 at 21:00

score 7 · Answer 1 · edited Sep 18 '13 at 10:28

7

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

edited Sep 18 '13 at 10:28

Jodrell

34,946
5
87
124

answered Sep 18 '13 at 09:58

cbeckner

1,808
15
17

You might as well use this overload, http://msdn.microsoft.com/en-us/library/gg696545(v=vs.103).aspx, and do `context.Database.SqlQuery("SELECT [KEY] ...` and scrub the `Cast` and `AsQueryable`. I'm happy to reward your efforts (+1) but this isn't the answer I was looking for when I set the bounty. – Jodrell Sep 18 '13 at 10:26
Understood completely. It looks like EF6 *may* have a way to do what you're after via UDF mappings, but I didn't experiment with it. Appreciate the +1. – cbeckner Sep 18 '13 at 10:47
Beware of the limit of the `IN` operator though - as far as I know SQL Server will throw an error if you use it with over 3,000 values – Xavier Poinas May 19 '15 at 09:22
@XavierPoinas how do you deal with the limit of `IN` operator? We sometimes run into this problem so I consider switching from this solution to using stored procedure. – rotman Dec 08 '16 at 13:42
@rotman I guess you could count your IN values and split the query (and do a union with all results) if you know you're going to be above the limit. But basically I only use this method if I know I'm going to be well under 3,000 values. – Xavier Poinas Dec 09 '16 at 14:41

score 2 · Answer 2 · edited May 23 '17 at 12:16

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

Let me extend that post.

Your first option was mine first as well - using SqlQuery I also needed to do more filtering, so instead of always writing full sql I used QueryBuilder, to which I made some changes and added more functions to fit my needs(I could upload it somewhere if needed): QueryBuilder

After I have found another idea which I implemented. Someone already mention it here, and that is to use SqlQuery that will return HashSet of Ids and that you can use it in EF queries with Contains. This is better but not most optimal since you need 2 queries and Id list 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 EF 6 now has something called Interceptors that can be used to implement queriable FTS, and it is pretty simple and generic(last post): EF 6 Interceptors for FTS. I have tested this and it works fine.

!! REMARK: EF Code First, even with version 6, does not support Custom Stored Procedures. There are only some for predefined CUD operations if I understood it well: Code First Insert/Update/Delete Stored Procedure Mapping, so it can't be done with it.

Conclusion: if you can use EF 6 go for third options, is gives all you need. If you are stucked with EF 5 or less, second option is better then first but not most optimal.

EF Interceptors is a good way to work around this, however with some query compositions/filtering, the FTS filter will end up being applied to a subquery, which doesn't work. You may need some complicated parsing/rewriting of the query. — Xavier Poinas, May 19 '15 at 09:24
You are right, I had one scenario of that happening(doing composed join and filtering by some type and by Full text search at the same time), so I had to simplify query in a way that join and FTS were first executed and then other done in memory. — borisdj, May 20 '15 at 12:49

A "Composable" Full Text Search with a Code First Model

2 Answers2

Linked