.NET Framework - Linq to Sql query help

Asked By ale

06-Jun-08 01:22 PM

I have the following database schema:

posts
-------
post_id
post_name


posts_tags
-------------
poast_id
tag_id


tags
-------
tag_id
tag_value


I need to select all posts records for provided tags. For example, give me
all posts that have tags "c#, asp.net, microsoft", or give me all posts that
have tags "sql, linq" etc.

I'm having trouble comming up with a Linq query to do that.

Thanks for your help,
Alex

07-Jun-08 03:23 AM

I don't have a lot of time so I can't dig deeper in this query, but
it's indeed not something that's really easy. In general, the results
are the same as: get all posts which aren't in the list of all posts
which don't have one of the tags.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

07-Jun-08 05:58 PM

I'm no expert but I've come up with something that seems to work, but
BEWARE - I've no idea how efficient it would be with large data sets
(probably not very!). However, it might give you some ideas. I've split it
up into a number of queries as it makes it easier to see what's going on,
but it's probably possible to combine them. Apologies if you prefer the
(and apologies too for the very unhelpful names I''ve used).

// Get sequence of tags wanted with no duplicates
var tagNames = textBox1.Text.Split(',').ToList().Distinct();
int numOfTags = tagNames.Count();

// Get sequence of ids of wanted tags (may want to do case-insensitive
comparisons here)
var tagids = tagNames.Join(tags, tn => tn, t => t.tag_value, (tn, t) =>
t.tag_id);

// Check that all tags are known
if (tagids.Count() != numOfTags)
{
MessageBox.Show("Not all tags were recognised");
return;
}

// Effectively get all post_tag entries for all the required tags and group
them by the post id
var posttags =
post_tags.Join(tagids, pt => pt.tag_id, t => t, (pt, t) => new {
pt.post_id, pt.tag_id}).
GroupBy(x => x.post_id);

// Each posttags entry consists of a Key (post_id) and a collection of tag
ids. We only want
// the ones with ALL the required tag ids.
var postids = posttags.Where(pts => pts.Count() == numOfTags).Select(pts =>
pts.Key);

// Finally convert the post ids into names
var postnames = postids.Join(posts, pi => pi, p => p.post_id, (pi, p) =>
p.post_name);

Good luck!
Chris Jobson