LINQ to SQL - Enabling Fulltext searching

LINQ to SQL allows you write your data access statements in your .Net code in LINQ which is then translated into SQL. LINQ supports many of the same constructs, predicates of SQL however some aren't supported.

One set of predicates that aren't supported are those for full text, i.e. CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE.

That means you can't write something like,

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.Contains("Description,Title","simon")

                 select s;

You can however do

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.description.Contains("simon")

                 select s;

So you might think that full text is supported. Unfortunately Contains is translated into a LIKE predicate with two wildcards, i.e.

select *

  from sessions

 where description like '%simon%'

So how do you get it working. Well the magic is in Inline Table Valued Functions. I've talked about them before when discussing performance of scalar functions. An inline table valued function is essentially a prameterised SQL statement, and importantly only one statement. This means the optimiser can merge the SQL Statement into the query that uses it.

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;

If you want you can extend this to limit the number of results from containstable. If you want to search different columns then you will need different functions as the colum list used by containstable cannot take a parameter.



-
Published 18 December 2008 10:52 by simonsabin
Filed under:

Comments

# Dew Drop - December 18, 2008 | Alvin Ashcraft's Morning Dew

Pingback from  Dew Drop - December 18, 2008 | Alvin Ashcraft's Morning Dew

07 April 2009 12:22 by Full Text Search in LINQ « DeBelog

# Full Text Search in LINQ « DeBelog

Pingback from  Full Text Search in LINQ « DeBelog

02 July 2010 06:49 by Confluence: Tech Stuff

# LINQ

Example of why you must examine the SQL statements produced by LINQ: link

30 January 2011 14:12 by Simons SQL Blog

# Entity framework support for table valued functions and thus full text

One of my most popular posts with over 10, 000 hits is how to enable full text when using LINQ to SQL

# Full Text Searching in Entity Framework / Calling a stored procedure from a EntityDataSource | MSDN @ EEYOGO

Pingback from  Full Text Searching in Entity Framework / Calling a stored procedure from a EntityDataSource | MSDN @ EEYOGO

# Entity Framework, Code First and Full Text Search | Ask Programming & Technology

Pingback from  Entity Framework, Code First and Full Text Search | Ask Programming & Technology

# How do you do full text search (FTS) with Linq to ADO.NET entity framework? | Ask Programming & Technology

Pingback from  How do you do full text search (FTS) with Linq to ADO.NET entity framework? | Ask Programming & Technology

# use Full Text Search (FTS) with LINQ | Coding and Programing

Pingback from  use Full Text Search (FTS) with LINQ | Coding and Programing