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

sql-server linq full-text-search
Edwin Jarvis picture Edwin Jarvis · Oct 22, 2008 · Viewed 22.7k times · Source

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?

Answer

John picture John · Dec 22, 2008

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;

Related questions

Hitting the 2100 parameter limit (SQL Server) when using Contains()

from f in CUSTOMERS where depts.Contains(f.DEPT_ID) select f.NAME depts is a list (IEnumerable<int>) of department ids This query works fine until you pass a large list (say around 3000 dept ids) .. then I …

Read More
Dynamic query with OR conditions in Entity Framework

I am creating an application that searches the database and allows the user to dynamically add any criteria (around 50 possible), much like the following SO question: Creating dynamic queries with entity framework. I currently have working a search that checks …

Read More
SqlDataReader - How to convert the current row to a dictionary

Is there an easy way to convert all the columns of the current row of a SqlDataReader to a dictionary? using (SqlDataReader opReader = command.ExecuteReader()) { // Convert the current row to a dictionary } Thanks

Read More