Entity Framework, Full-Text Search and Temporary Tables

Question

I have a LINQ-2-Entity query builder nested in various Where clauses depending on a rather complex search form. Works great so far.

Now I need to use the SQL Server full-text search index in some of my queries. Is it possible to add a search query directly to the LINQ query and get a rating as a selectable property?

If not, I can write a stored procedure to load a list of all line identifiers that match the full-text search criteria, and then use the LINQ-2-Entity query to load detailed data and evaluate other optional filter criteria in a loop per line. That would, of course, be a very bad idea in terms of performance.

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

Answer 1

I think I would suggest a hybrid approach.

• Write a stored procedure that returns all the necessary information.
• Match the entity with the results. An entity can be created for this single purpose. Alternatively, use version 4 of the Entity Framework, which allows you to display complex types to run the results of a procedure. The fact is that instead of trying to force the results of the procedure to existing types of entities, they will process them as their own type.
• Now you can create a LINQ to Entities query.

Request example:

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 not in my head, so the syntax may be wrong. An important point is the processing of search results as an object.

Alternatively: use the more flexible FTS system, which can create special filtering for each type when creating the index.

Answer 2

I saw this code for EF4:

 var query = context.ExecuteStoreQuery<Person>( "SELECT * FROM People WHERE FREETEXT(*,{0})", searchText ).AsQueryable(); 

This may be easier than creating a stored proc or UDP in some cases.