Using Full Text Search with LINQ

LINQ does not natively support full text search keywords (FREETEXT, CONTAINS etc).  Actually the compiler won’t complain about them but just translate everything to LIKE.

Here is the work around:

1. Create a table-valued function that takes in keyword as the parameter:

CREATE function [dbo].[SearchJobAll]
(@keywords nvarchar(4000))
returns table
AS    return (SELECT * FROM CONTAINSTABLE( dbo.job,*,@keywords))

2. Join this function as a table:

from l in db.Jobs join s in db.SearchJobAll(keyword) on l.JobID equals s.KEY select l;

(Note: CONTAINSTABLE return only 2 columns: KEY and RANK)

Advertisements
This entry was posted in ASP.NET, SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s