How to retrieve grandchild objects from a parent using linq

Question

I have several parent > child > grandchild relationships in my db schema. Usually, I have the parent and I want some information about the grandchildren. For example, I have a user who has a collection of social networks which have collections of friends. I find myself writing this code over and over again.

        var friends = new List<Friend>();
        foreach (var socialNetwork in user.UserSocialNetworks)
        {
            foreach (var friend in socialNetwork.Friends)
            {
                friends.Add(friend);
            }
        }

Is there a more elegant way to do this with linq?

What I'd really like to be able to do is "user.Friends" but I'd have to put a foreign key to user in the friend table and that doesn't smell right. Here is what that would look like:

User          {Id,..}
SocialNetwork {Id, UserId, ...}
Friend        {Id, SocialNetworkId, UserId, ... }

Thoughts?

Answer 1

You can write the code just once as a method on the User class:

partial class User
{
   public IEnumerable<Friend> Friends()
   {
     return from socialNetwork in this.UserSocialNetworks
            from friend in socialNetwork.Friends
            select friend;
   }
}

Alternatively, you can just use SelectMany():

var friends = user.UserSocialNetworks.SelectMany(socialNtwk=>socialNtwk.Friends);