Writing an extension method to help with querying many-to-many relationships

Question

I am trying to write an extension method in order to refactor a linq many-to-many query I'm writing. I am trying to retrieve a collection of Post(s) which have been tagged with any of the Tag(s) in a collection passed as a parameter to my method.

Here are the relevant entities along with some of their properties:

Post

Scalar Properties: PostID, PostDate

Navigation Property: PostTags

PostTag

Scalar Properties: PostTagID, PostID, TagID

Navigation Properties: Post, Tag

Tag

Scalar Properties: TagID

Navigation Property: PostTags

This is the query I'm currently using which works well:

public IEnumerable<Post> GetPostsByTags(IEnumerable<Tag> tags)
{
    return from pt in context.PostTags
           from t in tags
           where pt.TagID == t.TagID &&
                 pt.Post.PostDate != null
           orderby pt.Post.PostDate descending
           select pt.Post;               
}   

This is the (probably incorrect) start of the extension method I'm struggling to create:

public static IEnumerable<TResult> SelectRange<TSource, TResult>(
    this IEnumerable<TSource> collection,
    Func<IEnumerable<TSource>, IEnumerable<TResult>> selector)
{
    return selector(collection);
}

And the ideal simplification of the original query:

public IEnumerable<Post> GetPostsByTags(IEnumerable<Tag> tags)
{
    return from p in context.Posts
           where p.PostTags.SelectRange(x => ?) &&
                 p.PostDate != null                    
           orderby p.PostDate descending
           select p;
}

Any help in writing this extension method, or any other more efficient way to perform this query, will be greatly appreciated.

Answer 1

I think your original query is fine, you just need to handle duplicate posts. Add a distinct to the end. Or you can use the Any method like so.

public IEnumerable<Post> GetPostsByTags(IEnumerable<Tag> tags)
{
    return from p in context.Posts
           where p.PostTags.Any(pt => tags.Any(t => t.TagID == pt.TagID)) &&
                 p.PostDate != null                    
           orderby p.PostDate descending
           select p;
}

Edit - Added another Any statement

Answer 2

The most efficient way to achieve your goal is probably to use the built-in join clause which is dedicated to such many-to-many relationships :

from pt in PostTags
where pt.Post.PostDate != null
join t in tags on pt.TagID equals t.TagID
orderby pt.Post.PostDate descending
select pt.Post;

It is not "more correct" than the previous options : you already had something working when you posted your question. But it is surely the neater way to have it work in terms of syntax and performance.