Using SQL Full Text with LINQ

Working with a database is a fairly common scenario for many business applications.  You have to store that data somewhere right?  It’s been a while since I have done any database work at my awesome job, so I took it upon myself to play with SQL Server a few days ago for fun.  I have been getting more and more familiar with LINQ, so I chose to use some of the LINQ to SQL goodness. Let’s start with this piece of code to find some customer orders:
IQueryable<OrderEntity> orders = from order in _context.OrderEntities
where Description = "computer"
select order;
I set up LINQ statement, and it’s going to select all of my orders that have a description of computer.   But what I really want is to find all of the orders with the word computer IN them.  So let’s switch our statement now to use the LINQ translation of SQL contains:
IQueryable<OrderEntity> orders = from order in _context.OrderEntities
where Description.Contains("computer")
select order;
Great.  Now we are searching for all of the orders that have a description containing the word computer in it.  This is a great improvement over what we had before, but to be blunt, it still sucks.  Why?  Because everybody expects all searches to work like a Google search.  If I search for computer, it should bring back all things related to computer.  This is where full text search comes in.  You can read more about full text search here if you would like, but the summary of it is that it does a more linguistic search instead of a black and white search.  So let’s combine full text search into our LINQ statement.

……

Crap.  It’s not supported by LINQ.  What to do.  Well, after some pondering, I realize that what I’m doing in 99% of my time with full text search is using the CONTAINSTABLE function.  From a high level, CONTAINSTABLE is based on whatever search term(s) you pass in, and gives back as a result a table with the key, in this case our table primary key, and a rank of how closely the search term was able to match to the column(s) you searched against.  Unfortunately, this still doesn’t really get me any further because I still can’t call this from LINQ.  Hmmmm.

GOT IT!  What I can do is create a table based function and call that from LINQ.  So, I create a table based function called Orders_FullTextSearch and drag it into my LINQ data context.  I can now update my LINQ to SQL statement:
IQueryable<OrderEntity> orders = from order in _context.OrderEntities
join fts in _context.Order_FullTextSearch("computer")
on order.Id equals fts.Key
select order;
There we go, full text searching in LINQ.  You can expand on this example to use the FREETEXTTABLE as well.  I purposefully glossed over the SQL implementation of this because I wanted to focus on the coding part, but you can read more the functions I mentioned here.  Full text searching can be complicated to set up, but it’s really cool in action and can add some pretty nice features to any searching you might be doing in your code or database.  I should also mention that it may take some type of SQL guru to optimize and administrate the full text stuff.  But you can make your end user happy!

Share this post!

0 comments:

Post a Comment