LinqToSql query that spans a many-to-many relation?

Question

Let say i have the following schema

Content(Id, ....)
TagContent(TagId, ContentId)
Tag(TagId, Name)

Suppose I'd like to select all content records that have tag with name "test".

In SQL I would write:

select Content.Id 
from Content 
       join TagContent as TC on (TC.ContentId = Content.Id)
       Join Tag on (TC.TagId = Tag.Id)
where Tag.Name = 'Test'

Could you suggest how to write a similar query in Linq if you have only Table available? (I'd like to create an extension method Content.ByTag('tag') -> IQueryable )

I've only managed to create a query that use the sql exists statement instead of join. Which means that the queries are extremely inefficient.

My current inefficient solution looks as follows:

DataContext.Contents.Where(c => c.TagContents.Any(tc => tc.Tag.Name == "Test"))

NOTE: As I'd like to make the extension method on DataContext.Contents I won't have access to other tables that is DataContext.Tag and DataContext.ContentTag.

Answer 1

Something like this perhaps

var contentIds = from c in Content
                     join tc in TagContent on c.Id equals tc.ContentId
                     join t in Tag on tc.TagId equals t.Id
                     where t.Name == "Test"
                     select new 
                     {
                        ContentId = c.Id
                     };