LinqToSql and full text search - can it be done?

Question

Has anyone come up with a good way of performing full text searches (FREETEXT() CONTAINS()) for any number of arbitrary keywords using standard LinqToSql query syntax?

I'd obviously like to avoid having to use a Stored Proc or have to generate a Dynamic SQL calls.

Obviously I could just pump the search string in on a parameter to a SPROC that uses FREETEXT() or CONTAINS(), but I was hoping to be more creative with the search and build up queries like:

"pepperoni pizza" and burger, not "apple pie".

Crazy I know - but wouldn't it be neat to be able to do this directly from LinqToSql? Any tips on how to achieve this would be much appreciated.

Update: I think I may be on to something here...

Also: I rolled back the change made to my question title because it actually changed the meaning of what I was asking. I know that full text search is not supported in LinqToSql - I would have asked that question if I wanted to know that. Instead - I have updated my title to appease the edit-happy-trigger-fingered masses.

Answer 1

Unfortunately LINQ to SQL does not support Full Text Search.

There are a bunch of products out there that I think could: Lucene.NET, NHibernate Search comes to mind. LINQ for NHibernate combined with NHibernate Search would probably give that functionality, but both are still way deep in beta.

Answer 2

I've manage to get around this by using a table valued function to encapsulate the full text search component, then referenced it within my LINQ expression maintaining the benefits of delayed execution:

string q = query.Query;
IQueryable<Story> stories = ActiveStories
         .Join(tvf_SearchStories(q), o => o.StoryId, i => i.StoryId, (o,i) => o)
         .Where (s => (query.CategoryIds.Contains(s.CategoryId)) &&
            /* time frame filter */
           (s.PostedOn >= (query.Start ?? SqlDateTime.MinValue.Value)) &&
           (s.PostedOn <= (query.End ?? SqlDateTime.MaxValue.Value)));

Here 'tvf_SearchStories' is the table valued function that internally uses full text search