Is a join appropriate for accomplishing such functionality in LINQ to SQL?

Question

I'm writing an ASP.NET MVC site where I'm using LINQ to SQL to access my SQL Server database. In my database, I have the following tables:

Posts:

• PostID - int, PK, identity
• Text - nvarchar(MAX)
• PublishDate - datetime
• etc.

PostTags:

• PostTagID - int, PK, identity
• PostID - FK to PK to Posts table
• TagID - FK to PK to Tags table

Tags:

• TagID - int, PK, identity
• TagName - varchar(100)

Each Post must have at least 1 Tag, so it's a single-to-many relationship.

I'm trying to build a tag search function. I want to accept a Tag as a parameter and return the first 25 Posts that have that tag, ordered by PublishDate descending. Here's my current LINQ to SQL code:

    var query = (from post in db.Posts
                join posttag in db.PostTags
                on post.PostID equals posttag.PostID
                where posttag.Tag.TagName==tag
                select post).OrderByDescending(p=>p.DateOfPublish).Take(25);

However, I think that my code is wrong, because it doesn't seem to respect the single-to-many relationship between Posts and Tags.

How can I improve my code? Thanks in advance!

Answer 1

var query = (from post in db.Posts 
            where(
                    from posttag in db.PostTags
                    join tags in db.Tags
                    on posttag.TagID equals tags.TagID   
                    where tags.TagName == tag select posttag.PostID
                ).Contains(post.PostID) 
            orderby post.PublishDate descending 
            select post).Take(25);