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.
-
Source Click Here.
Post a Comment