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)

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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s