Entity Framework, full-text search and temporary tables

Question

I have a LINQ-2-Entity query builder, nesting different kinds of Where clauses depending on a fairly complex search form. Works great so far.

Now I need to use a SQL Server fulltext search index in some of my queries. Is there any chance to add the search term directly to the LINQ query, and have the score available as a selectable property?

If not, I could write a stored procedure to load a list of all row IDs matching the full-text search criteria, and then use a LINQ-2-Entity query to load the detail data and evaluate other optional filter criteria in a loop per row. That would be of course a very bad idea performance-wise.

Another option would be to use a stored procedure to insert all row IDs matching the full-text search into a temporary table, and then let the LINQ query join the temporary table. Question is: how to join a temporary table in a LINQ query, as it cannot be part of the entity model?

Answer 1

I think I would probably suggest a hybrid approach.

1. Write a stored procedure which returns all the information you need.
2. Map an entity to the results. The entity can be created for this sole purpose. Alternately, use version 4 of the Entity Framework, which allows mapping complex types to start procedure results. The point is that instead of trying to coerce the procedure results in to existing entity types, were going to handle them as their own type.
3. Now you can build a LINQ to Entities query.

Sample query:

var q = from r in Context.SearchFor("searchText)
        let fooInstance = (r.ResultType == "Foo") ?
                          Context.Foos.Where(f => f.Id == r.Id) : null
        where ((fooInstance == null) || (fooInstance.SpecialCriterion == r.SpecialCriterion))    
        select {

This is off the top of my head, so the syntax might not be right. The important point is treating search results as an entity.

Alternately: Use a more flexible FTS system, which can do the "special", per-type filtering when building the index.