CODEWITHSUNDEEP
.netlinqdatabase-design
DanielEli
DanielEli

Reputation: 3503

How to retrieve grandchild objects from a parent using linq

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?

Upvotes: 8

Views: 5291

Answers (2)

Jonathan
Jonathan

Reputation: 5018

I know this old, but I've recently faced the same thing, and an alternative is to go about it the opposite way. Instead of starting at user and drilling down, start at Friend and filter based on the parent (or grandparent). You would need the user's Id for this. When the hierarchy gets deeper, I find this more legible. Something like:

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

Upvotes: 0

Mark Cidade
Mark Cidade

Reputation: 100007

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);

Upvotes: 16

Related Questions