CODEWITHSUNDEEP
linq-to-sqlfull-text-search
RobertTheGrey
RobertTheGrey

Reputation: 8685

LinqToSql and full text search - can it be done?

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.

Upvotes: 5

Views: 1569

Answers (2)

LaserJesus
LaserJesus

Reputation: 8540

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

Upvotes: 5

Jon Limjap
Jon Limjap

Reputation: 95432

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.

Upvotes: 4

Related Questions