Thursday, December 18, 2008

Linq Subqueries

It's a rather simple problem, but a little difficult to figure out at first with Linq. The problem of subquerying. Let's say I have a FamilyMember class, that I've defined like this:

public class FamilyMember
{
public FamilyMember(string name)
{
Name = name;
}
public string Name { get; private set; }
}


Now, let's say that I've defined a Family class like this:

public class Family
{
public Family(string state, FamilyMember husband, FamilyMember wife)
{
State = state;
Members = new List<FamilyMember>();
Members.Add(husband);
Members.Add(wife);
}

public Family(string state, FamilyMember husband, FamilyMember wife, params FamilyMember[] children) :
this(state, husband, wife)
{
Members.AddRange(children);
}

public string State { get; private set; }

public List<FamilyMember> Members { get; private set; }
}


Now, let's say I add a whole bunch of families to a list:

// Create the family members.
FamilyMember glenn = new FamilyMember("Glenn");
FamilyMember debi = new FamilyMember("Debi");

FamilyMember daniel = new FamilyMember("Daniel");
FamilyMember amanda = new FamilyMember("Amanda");
FamilyMember rachel = new FamilyMember("Rachel");
FamilyMember joshua = new FamilyMember("Joshua");

FamilyMember matthew = new FamilyMember("Matthew");
FamilyMember shannon = new FamilyMember("Shannon");
FamilyMember elizabeth = new FamilyMember("Elizabeth");
FamilyMember abigail = new FamilyMember("Abigail");

FamilyMember david = new FamilyMember("David");
FamilyMember jennifer = new FamilyMember("Jennifer");

// Create the families.
Family springMortons = new Family("TX", glenn, debi);
Family bostonMortons = new Family("MA", daniel, amanda, rachel, joshua);
Family aggieMortons = new Family("TX", matthew, shannon, elizabeth, abigail);
Family houstonMortons = new Family("TX", david, jennifer);

// Make a list of all the families
List<Family> allFamilies = new List<Family>()
{ springMortons, bostonMortons, aggieMortons, houstonMortons };


Now that I have this list, I want to pull out all of the family members located in texas whose names begin with "D". How would I do this? It seems that Linq allows for nested subqueries, and they're rather simple to use once you get the hang of it. Here's the answer:

var dPeople = from family in allFamilies
where family.State == "TX"
from member in family.Members
where member.Name.StartsWith("D")
select member;


That's it! It's as simple as making two nested from statements. The first from statement will select the families, and the second will select the individuals within the families. The where clause immediately below the family selection will filter the families, and the where clause immediately below the member selection will filter the members. The resulting dPeople is an IEnumerable<FamilyMember>, and you can easily iterate through it to access each instance.

Happy coding!