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

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.

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

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.

I actually had to come up with my own solution to this issue because I am dealing with a data source that is NOT compatible with the Entity Framework (DBase 4 on an odbc connection) that has quite a few many to many table relations, and instead of having to write out a long drawn out join and group blocks of linq I created two extension methods

Now mind you I am only dealing with one direction reads from the datasource into essentially a dataset this program is only for historical data and does not accept new input or changes so I'm not sure how well these extension methods would hold up to any actual data updates inserts or deletes etc

(I've wrapped each parameter on it's own line for readability)

public static IEnumerable<TResult> ManyToManyGroupBy
    <TLeft, TRight, TMiddle, TLeftKey, TRightKey, TGroupKey, TResult>
    (
      this IEnumerable<TLeft> Left, 
      IEnumerable<TRight> Right, 
      IEnumerable<TMiddle> Middle, 
      Func<TLeft, TLeftKey> LeftKeySelector, 
      Func<TMiddle, TLeftKey> MiddleLeftKeySelector, 
      Func<TRight, TRightKey> RightKeySelector, 
      Func<TMiddle, TRightKey> MiddleRightKeySelector, 
      Func<TLeft, TGroupKey> GroupingSelector, 
      Func<TGroupKey, IEnumerable<TRight>, TResult> Selector
    )
{
  return Left
   .Join(Middle, LeftKeySelector, MiddleLeftKeySelector, (L, M) => new { L, M })
   .Join(Right, LM => MiddleRightKeySelector(LM.M), RightKeySelector, (LM, R) => new { R, LM.L })
   .GroupBy(LR => GroupingSelector(LR.L))
   .Select(G => Selector(G.Key, G.Select(g => g.R)));
}

public static IEnumerable<TResult> ManyToManySelect
    <TLeft, TRight, TMiddle, TLeftKey, TRightKey, TResult>
    (
      this IEnumerable<TLeft> Left, 
      IEnumerable<TRight> Right, 
      IEnumerable<TMiddle> Middle, 
      Func<TLeft, TLeftKey> LeftKeySelector, 
      Func<TMiddle, TLeftKey> MiddleLeftKeySelector, 
      Func<TRight, TRightKey> RightKeySelector, 
      Func<TMiddle, TRightKey> MiddleRightKeySelector, 
      Func<TLeft, TRight, TResult> Selector
    )
{
  return Left
   .Join(Middle, LeftKeySelector, MiddleLeftKeySelector, (L, M) => new { L, M })
   .Join(Right, LM => MiddleRightKeySelector(LM.M), RightKeySelector, (LM, R) => new { R, LM.L })
    .Select(LR => Selector(LR.L, LR.R));
}

They are fairly long methods but they cover join together two tables via the middle table as well as grouping by any particular Left item or a property of the left item

Using them would look like something like this (Like above I've wrapped each parameter on it's own line for readability)

  var EmployeesCoursesTest = Employees.ManyToManySelect
  (
   Courses, 
   CourseParticipations, 
   E => E.SS, 
   CP => CP.SS, 
   C => C.EVENTNO, 
   CP => CP.EVENTNO, 
   (E, C) => new
   {
    C.EVENTNO,
    C.START_DATE,
    C.END_DATE,
    C.HOURS,
    C.SESSIONS,
    Trainings.First(T => T.TRGID == C.TRGID).TRAINING,
    Instructors.First(I => I.INSTRUCTID == C.INSTRUCTID).INSTRUCTOR,
    Locations.First(L => L.LOCATIONID == C.LOCATIONID).LOCATION,
    Employee = E
   }
  );

  var EmployeesCoursesGroupByTest = Employees.ManyToManyGroupBy
  (
   Courses, 
   CourseParticipations, 
   E => E.SS, 
   CP => CP.SS, 
   C => C.EVENTNO, 
   CP => CP.EVENTNO, 
   E => E,
   (E, Cs) => new
   {
    Employee = E,
    Courses = Cs
   }
  );

Maybe it will help you out I myself tend to stay away from the query syntax so I work almost exclusively with the method syntax of Linq, so writing these kinds of extension methods are just a way i think now.