Linq selecting parent based on child criteria

Question

The big picture:

I'm working on a search form where the user can choose one or more criteria to filter the search results. One of the criteria is related to a child relationship.

I'm trying to create an extention method to Iqueryable<Parent> so I can use as part of my "chaining".

The method signature (as of now) is:

public static IQueryable<Parent> ContainsChild(this IQueryable<Parent> qry, int[]  childrenIDs)

The parent table and a child table:

Parent
  ParentID
  Name
  Description

Child
  ParentID (FK)
  AnotherID (from a lookup table)

Selection criteria:
int[] ids = new int[3] {1,2,3}; 

Usage would be something like this:

var parents = repository.All() //returns Iqueryable<Parent>
public IQueryable<Parent> Search(Search seach){
   if (search.Criteria1 != null){
      parents = parents.FilterByFirstCriteria(search.Criteria1);
   }
   if (search.ChildrenIDs != null){ //ChildrenIDs is an int[] with values 1,2,3
      parents = parents.ContainsChild(search.ChildrenIDs)
   }
}

What I'm trying to figure out is how to create the ContainsChild method that returns an IQueryable<Parent> where the parents have at least one child with the AnotherID in the ids array.

(I'm trying to use EF4 to accomplish this)

Any help fully appreciated.

Answer 1

Perhaps this:

public static IQueryable<Parent> ContainsChild(this IQueryable<Parent> qry,
   int[] childrenIDs)
{
   return qry.Where(p => p.Children.Any(c => childrenIDs.Contains(c.AnotherID)));
}

Edit

Just for fun another way which should give the same result:

public static IQueryable<Parent> ContainsChild(this IQueryable<Parent> qry,
   int[] childrenIDs)
{
   return qry.Where(p => p.Children.Select(c => c.AnotherID)
                                   .Intersect(childrenIDs).Any());
}

The generated SQL for the first version looks more friendly though, so I'd probably prefer the first version.