Tip: Using Full Text Search With a Table-Valued Function

I’ve been working with the SubSonic forums lately (CommunityServer) as I prep them for retirement (archiving, searchable). One thing I really need to is to be able to run accurate searches based on free text – so over the weekend I’ve been goofing around with Full Text indexing and it hit me that not a lot of people know a little trick you can pull with a Table-valued function.

SQL Functions
Also known as “UDFs”, functions are something that people don’t use too much. It’s understandable in our current climate of ODD – ORM Dependency Disorder (hey I *just* thought of that – kind of like it :) where all DB procedures (SPs or whatever) are generally avoided. I won’t get into that debate – but if you’re free to put a little programming in your DB, I’ll show you how you can get away from dropping business logic in there and raising it into your ORM while still flexxing the power of SQL Server.

There are 2 main function types in SQL Server 2005 that you can write (not counting system functions here):

• Scalar: returns a single, typed value
• Table-valued: returns an in-memory table of records

Scalar functions are very, very useful and you can use them to run calculations that you might want to re-use in your application – such as finding the distance between two points given their lat/long.

Table-valued functions are incredibly useful if you want to reuse functionality that returns one or more results – such as a Full Text search.

Hooking Search Up To a Function
In my last post I showed you how to hook up Full Text indexing, which allows you to do some really cool free text searches. Once you get that done, you can work up a Table-value Function that accepts the search term (this example is using the CommunityServer database):

ALTER FUNCTION [dbo].[SearchPosts] 
(    
    @search nvarchar(500)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT KEY_TBL.[KEY] as PostID,
       KEY_TBL.RANK as Relevance
    FROM 
       FREETEXTTABLE(cs_Posts, Body, 
       @search) AS KEY_TBL    
)

Notice that in this code I’m purposely avoiding joining any tables – I’m only exposing what the function gives me: a Primary Key of the searched record (which in this case is the PostID) and a RANK. I’ll work with the returned data in other ways in a moment.

Note: I could use CONTAINS or other method of search here – this part’s up to you. I like FREETEXTTABLE because it works for me

Using Linq To Sql with Table-value Functions
Linq to Sql supports table-valued functions in the same way it supports tables – you can use them in your queries just like you would any other class. To see how this works I’ll work up a Linq to Sql class and pull in CommunityServer’s posts, as well as my groovy function:

I’ve named my DataContext “DB” inside of the “CS” class just to keep things simple – now I can query my search results (using a Console App here):

static void Main(string[] args) {

    CS.DB db = new CS.DB();

    var results=from s in db.SearchPosts("distinct query")
                select s;

    Console.WriteLine(results.Count());
    Console.ReadLine();
}

And I get back a bunch of results – lots of people have written posts in our forums, evidently, on the subject of DISTINCT queries using SubSonic:

That’s not all that useful – but I can tweak this a bit and pull in some relevant information – as well as restricting the whole thing to the top 20 records:

static void Main(string[] args) {

    CS.DB db = new CS.DB();

    var results = from s in db.SearchPosts("distinct query")
                  join p in db.cs_Posts on s.PostID equals p.PostID
                  orderby s.Relevance descending
                  select new
                  {
                      Author=p.PostAuthor,
                      Date=p.PostDate,
                      Subject=p.Subject,
                      Rank=s.Relevance
                  };
    foreach (var post in results.Take(20)) {
        Console.WriteLine("Author: " + post.Author + "; Title: " + post.Subject);
    }
    Console.ReadLine();
}

This works perfectly:

Using SQL Profiler I can take a look under the hood to make sure Linq to Sql isn’t running away with my database:

exec sp_executesql N'SELECT TOP (20) [t1].[PostAuthor] AS [Author], 
[t1].[PostDate] AS [Date], [t1].[Subject], [t0].[Relevance] AS [Rank]
FROM [dbo].[SearchPosts](@p0) AS [t0]
INNER JOIN [dbo].[cs_Posts] AS [t1] ON [t0].[PostID] = [t1].[PostID]
ORDER BY [t0].[Relevance] DESC',N'@p0 nvarchar(14)',@p0=N'distinct query'

Notice how everything is parameterized as it should be? This is good stuff!

Summary
Many people don’t like diddling with StoredProcedures and Functions because, to them, “business logic will bleed in”. To some degree I can see why people think that – and again I won’t debate the point :). I think that this is pretty biz-logic free here, and shows a great way to push the search capabilities into application.