full text index + joins + linq : how to do this properly?

Question

SO really there are two parts of the question:

1) how to use CONTAINSTABLE() with bunch of inner joins?

Suppose there are two tables:

Item (ItemID, CategoryID, Name)
Category (CategoryID, ParentCategoryID, Name)

using "dumb" like search this looks like this:

create view [db].[vItem]
as
select i.*, c1.Name as SubCategory, c2.Name as Category, c3.Name as Department
from Item i
inner join category c1 on(c1.CategoryID=i.CategoryID)
inner join category c2 on(c1.ParentCategoryID=c2.CategoryID)
inner join category c3 on(c2.ParentCategoryID=c3.CategoryID)

then do:

select *
from vItem
where (Name like '%[word 1]%' or SubCategory like '%[word 1]%' or Category like '%[word 1]%' or Department like '%[word 1]%') [and ... word n]

I could drop the view and inner join 4 times on CONTAINSTABLE() but thats a) fugly b) not the same thing (each key table will need to have all the search terms in order to have any result at all)

2) How to use all of the above in query language. Again, assuming I just use like approach - its very simple.

from i in db.VItem
where i.Name.Contains("word 1") ...

How to do this with full text search?

Answer 1

For part 1): Create your view as an indexed view

create unique clustered index vItem_ItemID 
    on db.vItem(ItemID)

then create the fulltext index on the columns of that view rather than the base table.

create fulltext index on db.vItem
    (Name, SubCategory, Category, Department)
    key index vItem_ItemID 

Once that's created, you can:

select v.*
    from containstable(db.vItem, *, '"word 1"') ct
        inner join db.vItem v
            on ct.[key] = v.ItemID

I'll have to leave part 2) for someone else as I have little linq experience.