How to get grandchild objects from parent using linq

Question

I have several parent> child> grandchildren in my db schema. Usually I have a parent and I need information about my grandchildren. For example, I have a user who has a collection of social networks that have collections of friends. I find myself writing this code again and 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 really would like to do is "user.Friends", but I would have to put the foreign key to the user in the friends table, and that doesn't smell right. Here's what it looks like:

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

Thoughts?

Answer 1

You can write code only once as a method of 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 simply use SelectMany() :

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

Answer 2

I know this old, but recently I came across the same thing, and the alternative is to do the opposite. Instead of starting with the user and drilling, start with Friend and filter based on the parent (or grandfather). To do this, you will need a user ID. As the hierarchy goes deeper, I find it more picky. Something like:

 return _db<your entities>.Friend .Where(f => f.SocialNetwork.User.Id == userId) .ToList();