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.
-