CODEWITHSUNDEEP
c#linq
TPeel
TPeel

Reputation: 27

Map list of children to parent object from join table with LINQ

I've searched and can't find a similar question where three tables come into play. I have three tables: Parent, Child, and ParentChild. ParentChild is a many-to-many join table that lets you set multiple children to multiple parents. I'm using .Net Core 5.

Here are the dto classes for the tables:

public class Parent {
    public int Id { get; set; }
    public int UserId { get; set; }
    public List<Child> Children { get; set; }
}

public class Child {
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ParentChild {
    public int Id { get; set; }
    public int ParentId { get; set; }
    public int ChildId { get; set; }
}

Here's my linq statement:

var parentChildGroup = from parent in parentList 
                    join parentChild in parentChildList 
                    on parent.Id equals parentChild.ParentId
                    join child in childList on parentChild.ChildId equals child.Id
                    group child by child.Id into newGroup 
                    select new { 
                        Children = newGroup, 
                    };

I've gone through several different iterations based on examples I've found online and I've created a .Net fiddle with the full code:

https://dotnetfiddle.net/Hh12V3

What I want is a list of all Parent objects with a child list of all Child objects.

So, I should be able to iterate through Parent.Children for example. I just can't figure out how to insert the group of Child objects into the Children property of the Parent. It's probably simple and I've made this overly complicated. Any help would be appreciated.

Thanks!

Upvotes: 0

Views: 1676

Answers (2)

Ibram Reda
Ibram Reda

Reputation: 2820

Change the model of the parent to contains list of children

public class Parent {
    public int Id { get; set; }
    public int UserId { get; set; }
    public virtual List<Child> Children {get;set;} // <<<<<<<<<<<<
}

then you can select it as following

var parentChildGroup =parentList.Select(p=>
{
    p.Children = parentChildList.Where(pc=>pc.ParentId == p.Id)
                                .SelectMany(pc=>childList.Where(c=>c.Id == pc.ChildId))
                                .ToList();
    return p;
});

Upvotes: 2

Serge
Serge

Reputation: 43860

if you use net5 this should be enough


var parentChildGroup = db.Parents.Include(c=> c.Children).ToList();

Upvotes: 0

Related Questions