Linq Multi-level Dynamic where clause

Question

let me start by saying that I've searched and read a lot of threads here concerning dynamic where clauses, as well as the ScottGu's blog and the PredicateBuilder class from Albahari, but I'm not sure how I could correctly apply any of these methods to my case. Somehow, I can't wrap my head around it.

I have the following code, which works when done "statically" like this:

var persons = from father in fathers
              select new                                
              {
                 Count = father.Sons
                                    .Select(son => son)
                                    .Where(son => son.Skills.Any(skill => skill.SkillType == "Languages" && skill.Name == "French"))
                                    .Where(son => son.Skills.Any(skill => skill.SkillType == "Sport" && skill.Name == "Football"))
                                    .Count(),

                 Name = father.Name
               };

However, I would like to have the where clauses generated at runtime. A father object has a collection of Son objects, which in turn has a collection of Skills objects. As the query shows, I would like to know the names of each father and the number of their sons that have a certain set of skills. The set of skills will be selected at runtime, so even though in the example, we have just two set of skills (2 where clauses), it could be 10 or any number of clauses at runtime.

I think my biggest problem is that I can't seem to adapt the examples given to the answers here on StackOverflow to my situation, since I need to get information from the top level(father) as well as information from the 3rd level (skills) pertaining to information on the 2nd level(sons).

If needed, I will post sample code of what I've tried already tomorrow. I can't do it now, since I am in somewhat of a rush. Any help will be very appreciated.

EDIT:

What I need is a way to concatenate the where clauses at runtime, depending on how many filter criteria have been selected by the user. The different filter conditions are gotten from an external source and built at run time. So for example:

In scenario one, there could be 3 criteria, say criteria1, criteria2 and criteria3 that are selected by the user. Scenario 2 could have 5 criteria, say criteria1, criteria2, ..., criteria5. A third scenario could have 10 criteria, say criteria1, criteria2, ... , criteria10. What I need is to be able to do the following for each of the scenarios, without knowing beforehand if there will be 3, 5, 10, or whatever criteria.

Scenario one:

var persons = from father in fathers
              select new                                
              {
                 Count = father.Sons
                                    .Select(son => son)
                                    .Where(criteria1)
                                    .Where(criteria2)
                                    .Where(criteria3)
                                    .Count(),

                 Name = father.Name
               };

Scenario 2:

var persons = from father in fathers
              select new                                
              {
                 Count = father.Sons
                                    .Select(son => son)
                                    .Where(criteria1)
                                    .Where(criteria2)
                                    .Where(criteria3)
                                    .Where(criteria4)
                                    .Where(criteria5)
                                    .Count(),

                 Name = father.Name
               };

Scenario 3:

var persons = from father in fathers
                  select new                                
                  {
                     Count = father.Sons
                                        .Select(son => son)
                                      .Where(criteria1)
                      .Where(criteria2)
                      .Where(criteria3)
                      .Where(criteria4)
                      .Where(criteria5)                   
                      .Where(criteria6)
                      .Where(criteria7)
                      .Where(criteria8)
                      .Where(criteria9)
                      .Where(criteria10)
                                  .Count(),
                     Name = father.Name
                   };

Answer 1

The answer by jonnyGold is good, but it requires Son objects to have a reference to the father object. Here's a solution that doesn't require that:

var query = from father in fathers
            from son in father.Sons
            select new {father, son};

foreach (Skill skillCriterion in CriterionSkills)
{
    var capturedSkillCriterion = skillCriterion;
    query = query.Where(fs => fs.son.Skills.Any(
        skill => skill.SkillType == capturedSkillCriterion.SkillType && 
                 skill.Name == capturedSkillCriterion.Name));         
}         

var persons = from fs in query
              group fs by fs.father into g 
              select new                                 
              { 
                 Count = g.Count(),
                 Name = g.Key.Name
              };

Answer 2

var sons = fathers.SelectMany(f => f.Sons);

foreach(Skill skillCriterion in CriterionSkills)
{
    sons = sons.Where(son => son.Skills.Any(skill => skill.SkillType == skillCriterion.SkillType && skill.Name == skillCriterion.Name));
}

// we need to assume some sort of formal father-son relationship
var persons = from son in sons
              group son by new {son.Father.ID, son.Father.Name} into g
              select new
              {
                g.Key.Name,
                g.Count()
              };

Answer 3

Then design your queries where to handle the dyanmic nature of the filter. For example I have created father and sons where the sons can play football or volleyball. Now my query will take the dyanmic request of say football only...here is how I do it:

void Main()
{
   bool findFootballers   = true;
   bool findVolleyBallers = false;

   var Fathers = new List<Father>()
   {
      new Father() { Name = "Frank SR", Sons = new List<Son>() { new Son() { Name = "Bob", PlaysFootball = true }, new Son() { Name = "Frank", PlaysVolleyBall = true } } },
      new Father() { Name = "Knute", Sons = new List<Son>() { new Son() { Name = "Mean Jo Green", PlaysFootball = true }, new Son() { Name = "McMann", PlaysFootball = true } } }
   };


   Fathers.Where (f => (findFootballers == false) ? true : f.Sons.Any (s => s.PlaysFootball == true))
          .Where (f => (findVolleyBallers == false) ? true : f.Sons.Any (s => s.PlaysVolleyBall == true))
          .Select( f => new
                       {
                       Name = f.Name,
                       TargetSportSons = string.Join(", ", f.Sons
                                                            .Where (s => (findFootballers == false) ? true : s.PlaysFootball)
                                                            .Where (s => (findVolleyBallers == false) ? true : s.PlaysVolleyBall)
                                                            .Select (s => s.Name))
                       }
                 )
            .ToList()
            .ForEach(fs => Console.WriteLine ("Father {0} has these sons {1} who play {2}", fs.Name, fs.TargetSportSons, (findFootballers ? "Football" : "VolleyBall ")));

// Output
// Father Frank SR has these sons Bob who play Football
// Father Knute has these sons Mean Jo Green, McMann who play Football

}

public class Son
{
   public string Name { get; set; }
   public bool PlaysFootball { get; set; }
   public bool PlaysVolleyBall { get; set;}
}


public class Father
{
   public string Name { get; set; }
   public List<Son> Sons = new List<Son>();

}

// Define other methods and classes here