Full Text Search in Linq

Question

There's no full text search built into Linq and there don't seem to be many posts on the subject so I had a play around and came up with this method for my utlity class:

public static IEnumerable<TSource> GenericFullTextSearch<TSource>(string text, MyDataContext context)
{
    //Find LINQ Table attribute
    object[] info = typeof(TSource).GetCustomAttributes(typeof(System.Data.Linq.Mapping.TableAttribute), true);
    //Get table name
    String table = (info[0] as System.Data.Linq.Mapping.TableAttribute).Name;
    //Full text search on that table
    return context.ExecuteQuery<TSource>(String.Concat("SELECT * FROM ", table, " WHERE CONTAINS(*, {0})"), text);
}

And added this wrapper to each partial Linq class where there is a full text index

public static IEnumerable<Pet> FullTextSearch(string text, MyDataContext context)
{
    return (LinqUtilities.GenericFullTextSearch<Pet>(text, context) as IEnumerable<Pet>);
}

So now I can do full text searches with neat stuff like

var Pets = Pet.FullTextSearch(helloimatextbox.Text, MyDataContext).Skip(10).Take(10);

I'm assuming only a very basic search is necessary at present. Can anyone improve on this? Is it possible to implement as an extension method and avoid the wrapper?

Answer 1

The neatest solution is to use an inline table valued function in sql and add it to your model

http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx

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;