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;

Answer 2

I was pretty frustrated with the lack of clear examples... especially when there are potentially large data sets and paging is needed. So, here's an example that hopefully encompasses everything you might need :-)

create function TS_projectResourceSearch
    (   @KeyStr nvarchar(4000), 
        @OwnId int,
        @SkipN int,
        @TakeN int )
    returns @srch_rslt table (ProjectResourceId bigint not null, Ranking int not null )
    as 
    begin

        declare @TakeLast int
        set @TakeLast = @SkipN + @TakeN
        set @SkipN = @SkipN + 1

        insert into @srch_rslt  
        select pr.ProjectResourceId, Ranking
        from 
        (
            select t.[KEY] as ProjectResourceId, t.[RANK] as Ranking, ROW_NUMBER() over (order by t.[Rank] desc) row_num
            from containstable( ProjectResource,(ResourceInfo, ResourceName), @KeyStr )
            as t        
        ) as r
        join ProjectResource pr on r.ProjectResourceId = pr.ProjectResourceId
        where (pr.CreatorPersonId = @OwnId
            or pr.ResourceAvailType < 40)
            and r.row_num between @SkipN and @TakeLast
        order by r.Ranking desc 

        return
    end
    go


    select * from ts_projectResourceSearch(' "test*" ',1002, 0,1)

Enjoy, Patrick