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

You can just do something like this

    var results = (from tags in _dataContext.View_GetDeterminationTags
                   where tags.TagName.Contains(TagName) ||
                   SqlMethods.Like(tags.TagName,TagName)
                   select new DeterminationTags
                   {
                       Row = tags.Row,
                       Record = tags.Record,
                       TagID = tags.TagID,
                       TagName = tags.TagName,
                       DateTagged = tags.DateTagged,
                       DeterminationID = tags.DeterminationID,
                       DeterminationMemberID = tags.DeterminationMemberID,
                       MemberID = tags.MemberID,
                       TotalTagged = tags.TotalTagged.Value
                   }).ToList();

Notice where TagName.Contains also the SQLMethods.Like just do a using

using System.Data.Linq.SqlClient;

to gain access to that SQLMethods.

Answer 2

One dangerous/unoptimal issue regarding your query is that the .Skip().Take() will be performed clientside, not serverside. So if you do a FTS that returns 10^6 results and you want to just have the first 10, all 10^6 of them will be returned from the database, and only then will you perform the filtering.

Answer 3

dswatik - the reason for wanting full text search is that .contains translates to

SELECT * FROM MYTABLE WHERE COLUMNNAME LIKE '%TEXT%'

Which ignores any indexes and is horrible on a large table.

Answer 4

Actually this was asked before here check this question out

http://stackoverflow.com/questions/224475/is-it-possible-to-use-full-text-search-fts-with-linq

Answer 5

A slighty nicer method (takes rank into effect) using CONTAINSTABLE

String pkey = context.Mapping.GetTable(typeof(TSource)).RowType.DataMembers.SingleOrDefault(x => x.IsPrimaryKey).Name;
string query = String.Concat(@"SELECT *
    FROM ", table, @" AS FT_TBL INNER JOIN
    CONTAINSTABLE(", table, @", *, {0}) AS KEY_TBL
    ON FT_TBL.", pkey, @" = KEY_TBL.[KEY]
    ORDER BY KEY_TBL.[RANK] DESC");
return context.ExecuteQuery<TSource>(query, text);

Answer 6

I've been trying to solve the exact problem. I like to write my SQL logic in my LINQtoSQL but I needed a way to do Full Text Search. right now I'm just using SQL functions and then calling the user-defined functions inline of the linq queries. not sure if that's the most efficient way. what do you guys think?

Answer 7

Hi megatoast

Inline? , do you mean a table function? i.e. select... from dbo.fnSomeFunction(....) and then in the argument you specify the text and other?

Answer 8

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