Tag filtering Query using T-SQL and Linq-to-SQL?

Question

I'm trying to figure out how to allow a user to enter in a string of tags (keywords separated by spaces) in a textbox to filter a grid of results.

Here are the tables:

---

PACKETS
*PacketID
Name

---

PACKETTAGS
*PacketTagID
PacketID
TagID

---

Tags
*TagID
Name

---

Here is the basic query without the WHERE parameters:

SELECT     
       Packets.Name, Tags.Name AS Tag, PacketTags.PacketTagID
FROM         
       Packets 
INNER JOIN
       PacketTags ON Packets.PacketID = PacketTags.PacketID 
INNER JOIN
       Tags ON PacketTags.TagID = Tags.TagID

I need to filter out all the Packets that don't have tags that match any of the words BUT ALSO ONLY include the Packets that have the tags entered in the string of text (spaces separate the tags when entered into the textbox)

I'm starting with the basics by figuring this out in t-SQL first but ultimately I need to be able to do this in Linq-to-SQL

Answer 1

Assuming you have a list of your tags in memory:

var query = from p in DataContext.Packets 
            where p.Tags.Intersect(listOfMustHaveTags).Count() == listOfMustHaveTags.Count()
            select p;

I'm using intersect here to check the must-have tags set is fully contained. Maybe there's an even simpler solution.

Answer 2

Create a function that takes in the space delimited list of tags and returns a table. INNER JOIN your query above to the result of that function.

There are several functions written that do this. Here are two:

http://tim.mackey.ie/SQLStringSplitFunction.aspx

http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx

The function is also callable through linq to sql.