c# - Many to Many Query in Entity Framework 4

Question

I have have a many to many relationship in my database. The two end tables are BlogPost and Item and the table in the middle is ItemBlogPost. I need to get back all of the BlogPosts related to a specific item. In SQL I would do it like this:

SELECT BlogPost.*
FROM BlogPost
    JOIN ItemBlogPost ON BlogPost.ID = ItemBlogPost.BlogPost_ID
WHERE ItemBlogPost.Item_ID = @Item_ID

In C# I have something similar:

IQueryable<BlogPost> itemBlogPosts = from b in connection.BlogPosts
                                     where b.Items == item.ID 
                                     orderby b.Content.CreateDate descending
                                     select b;

However, the line marked b.Items doesn't give me a list of the Item properties and there is no b.ItemBlogPost to look at the intermediary table. I also tried doing b.Items.Contains(item) but that also failed. How can I make this work in LINQ to EF4?

Answer 1

What about this:

var itemBlogPosts = from i in connection.Items
                    from b in i.BlogPosts // I suppose you have a nav. property on Item
                    where i.Id == itemId
                    select b; 

The same query can be also defined by:

var itemBlogPosts = connection.Items
                              .Where(i => i.Id == itemId)
                              .SelectMany(i => i.BlogPosts);