Is it possible to use Full Text Search (FTS) with LINQ?

I wonder if is possible to use FTS with LINQ using .NET Framework 3.5. I'm searching around the documentation that I didn't find anything useful yet.

Does anyone have any experience on this?

Yes. However you have to create SQL server function first and call that as by default LINQ will use a like.

This blog post which will explain the detail but this is the extract:

To get it working you need to create a table valued function that does nothing more than a CONTAINSTABLE query based on the keywords you pass in,
create function udf_sessionSearch
      (@keywords nvarchar(4000))
returns table
as
  return (select [SessionId],[rank]
            from containstable(Session,(description,title),@keywords))
You then add this function to your LINQ 2 SQL model and he presto you can now write queries like.
    var sessList = from s   in DB.Sessions
                   join fts in DB.udf_sessionSearch(SearchText) 
                   on s.sessionId equals fts.SessionId
                 select s;

No. Full text search is not supported by LINQ To SQL.

That said, you can use a stored procedure that utilizes FTS and have the LINQ To SQL query pull data from that.

if you do not want to create joins and want to simplify your C# code, you can create SQL function and use it in "from" clause:

CREATE FUNCTION ad_Search
(
      @keyword nvarchar(4000)
)
RETURNS TABLE
AS
RETURN
(
      select * from Ad where 
      (CONTAINS(Description, @keyword) OR CONTAINS(Title, @keyword))
)

After updating your DBML, use it in linq:

string searchKeyword = "word and subword";
var result = from ad in context.ad_Search(searchKeyword)
                 select ad;

This will produce simple SQL like this:

SELECT [t0].ID, [t0].Title, [t0].Description
FROM [dbo].[ad_Search](@p0) AS [t0]

This is works in search by several columns as you can see from the ad_Search function implementation.

I don't believe so. You can use 'contains' on a field, but it only generates a LIKE query. If you want to use full text I would recommend using a stored proc to do the query then pass it back to LINQ

Is it possible to use Full Text Search (FTS) with LINQ?

Related

Recent Posts