LinqToSql Filter EntitySet

I am working on a WP7 application using Linq To Sql. I have used Linq, but this is the first I have used Linq to Sql. I am having an issue with filtering data in an EntitySet. I maybe doing it wrong I have no clue. What I have right now works, but I need to get one of the EntitySets filtered.

I have 4 tables. The Parent, Child, Grandchild, and a ParentChild linking table. When I query ParentChild, I get back the ParentChild entity and I can iterate through the Parent, Child and Grandchild entities just fine. What I want to be able to do is filter the Grandchild entity.

Lets say I have a father and mother in the Parent table. Then I have a son and daughter in the Child table. Then a grandson and granddaughter in the Grandchild table. Of course there are normal associations, etc.

I want to return the father, which also gets me all the associated tables just fine. The problem that I have is with filtering on the Grandchild. Let's say I want just the grandson and have a field for sex. How can I do this? I just can't seem to figure it out.

Here is the code I am using which works fine, but it pulls all the grandchildren.

IQueryable<ParentChild> parentChild = from ParentChild c in DataContext.ParentChild
                                              where c.ParentId == this.parentId
                                              select c;

foreach (Grandchild grandchild in parentChild.SelectMany(parent => parent.Child.Grandchild))
{
     Console.WriteLine(grandchild.Name);
}

So if I do this:

IQueryable<ParentChild> parentChild = from ParentChild c in DataContext.ParentChild
                                      where c.ParentId == this.parentId && c.Child.Grandchild.Any(a => a.Sex == "F")
                                      select c;

foreach (Grandchild grandchild in parentChild.SelectMany(parent => parent.Child.Grandchild))
{
     Console.WriteLine(grandchild.Name);
}

I get the parent, but I only get the children that have female grandchildren. I want the parent, all the children (even if they don't have female grandchildren or don't have any grandchildren) and only the female grandchildren.

回答1:

After much trial and error and searching, I found the answer. I have to use the AssociateWith option.

DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.AssociateWith<Child>(c => c.Grandchild.Where(p => p.Sex == "F"));

this.DataContext.LoadOptions = dataLoadOptions;

回答2:

As long as you've got your foreign keys set up correctly in SQL; LINQ to SQL will be able to give you association properties that match your foreign key relationships.

If your foreign keys are set up you'll be able to do the following...

var query = from p in DataContext.Parent            
            //make sure they have at least 1 female grandchild
            where p.GrandChilds.Any(gc => gc.IsFemale)
            select p;

I've made some assumptions about the names in your datamodel, but you get the idea. :-)