Eager loading / prefetching many-to-many without LoadOptions - Linq to Sql

Question

I've got a situation where I need to prefetch some entities through a many-to-many relationship. So it's like the classic BlogPost <- BlogPostTag -> Tag situation.

Yes, I'm aware of LoadOptions but I can't use it because it's a web application and I'm using the one datacontext per request pattern.

It also seems you can't use projection to prefetch many-to-many relationships. Yes? No?

I want to return IQueryable<Tag> based on a set of Blogs. The best I can do is get it to return IQueryable> by doing the following:

public IQueryable<Tag> GetJobsCategories(IQueryable<BlogPost> blogPosts)
{
    var jobCats = from bp in blogPosts
                  select bp.BlogPostTags.Select(x => x.Tag);

    return jobCats;
}

Can I flatten that? Am I missing something obvious? Is there another approach I can take?

And no, I can't change ORMs ;-)

Answer 1

This will work, you can just drill down in the linq query

public IQueryable<Tag> GetJobsCategories(IQueryable<BlogPost> blogPosts)
{
    return from bp in blogPosts
           from tag in bp.BlogPostTags
           select tag;
}

If you declare the method as such:

public static IQueryable<Tag> GetJobsCategories(this IQueryable<BlogPost> blogPosts)

You can use it as extension method on queryables. For instance

myContext.BlogPosts.GetJobsCategories()