Many To Many Relationship EF 6 Parents Players

Question

Basically, I have a player app that now needs to link multiple children to multiple parents.
I have for example a table

Player could have
1 Test Player 1
2 Test Player 2

Parents Table
1 DAD
2 Mom

ParentsFriends Link Table
Player Id Parent ID
1 1
2 1
1 2

So for example the json should show the parents first as root nodes then the children they can both see

  public IEnumerable<ParentsFriend> GetAllParentsFriends(int Id)  
{  
                var q = from parent in _db.ParentsFriends  
                        from children in _db.Players  
                        where parent.PlayerId == children.Id  
                        select new ParentFriendViewModel  
                        {     
`                          Parent1   
                            Parent 2      
                            Children = children.  
                        }  
}  
  

I want the json to output simply this this could be up to 4 or 5 parents?
Parent1
Parent 2
Children=.......

And also what happens say for example if the Dad Mom can only be allowed to see one player child

Edit2
This is to show the type of json i want to output

{  
The root would show all the parents first then the children in a child node  
  
  "id": 12,  
  "title": "Murry Gold Berg",  
  "description": "The best dad around",  
   
  "category": "sports",  
  "thumbnail": "https://dummyjson.com/image/i/products/12/thumbnail.jpg",  
   ///All parents should be outputted here first then all the children below them?  
  
   "id": 13,  
  "title": "Berverly Gold Berg",  
  "description": "The best mom around",  
   
  "category": "sports",  
  "thumbnail": "https://dummyjson.com/image/i/products/12/thumbnail.jpg",  
  "Children": [  
  "Child1":  
  {  
  "Name": "Barry Gold Berg",  
  "AGE":18,  
    .....  
  },  
  "Child2":  
  {  
  "Name": "Adam Gold Berg",  
  "AGE":15,  
    .....  
  }  
  ]  
}  

Answer 1

@NOVATROOP77 , thanks for the feedback. based on my attempt, I try to make a code example to get you wanted, but not all you wanted.

Since json doesn't allow the same key in one object, therefore, the following json string is not regular, I also meet the error when I want to use c# code to convert to it.

   "id": 12,  
   "title": "Murry Gold Berg",  
   "description": "The best dad around",  
       
   "category": "sports",  
   "thumbnail": "https://dummyjson.com/image/i/products/12/thumbnail.jpg",  
    ///All parents should be outputted here first then all the children below them?  
      
    "id": 13,  
   "title": "Berverly Gold Berg",  
   "description": "The best mom around",  
       
   "category": "sports",  
   "thumbnail": "https://dummyjson.com/image/i/products/12/thumbnail.jpg",  

We need to use json array to replace it to avoid the error.

Here is a code example you could refer to.

 TestEntities dbcontext = new TestEntities();  
            var result1 = (from parent in dbcontext.ParentsFriends.ToList()  
                           join p in dbcontext.Parents on parent.ParentId equals p.Id  
                           select new  
                           {  
                               p.Id,  
                               p.Title,  
                               p.description,  
                               parent.PlayerId  
  
                           }).Distinct().ToList();  
            var newresult1 = (from m in result1  
                      select new  
                      {  
                          m.Id,  
                          m.Title,  
                          m.description  
                      }).Distinct();  
                    
            var result2 = (from p in result1  
                          join children in dbcontext.Players on p.PlayerId equals children.Id  
                        select new  
                        {  
                            children.Name,  
                            children.Age  
                        }).Distinct().ToList();  
            string json = JsonConvert.SerializeObject(newresult1);  
            JObject total = JObject.Parse(@"{}");  
            JArray jsonArray = JArray.Parse(json);  
            total.Add("Parents", jsonArray);  
            string json1= JsonConvert.SerializeObject(result2);  
            JArray jsonArray1 = JArray.Parse(json);  
  
           
            int i = 1;  
            JObject child = JObject.Parse(@"{}");  
            foreach (JObject item in jsonArray1)  
            {  
  
                child.Add("child" + i,item);  
                i++;  
              
            }  
            total.Add("Children", child);  
            Console.WriteLine(total.ToString());  

Updated for Deserializing the json to the object:

         Root myDeserializedClass = JsonConvert.DeserializeObject<Root>(total.ToString());  

Class:

 public class Child1  
    {  
        public int Id { get; set; }  
        public string Title { get; set; }  
        public string description { get; set; }  
    }  
  
    public class Child2  
    {  
        public int Id { get; set; }  
        public string Title { get; set; }  
        public string description { get; set; }  
    }  
  
    public class Children  
    {  
        public Child1 child1 { get; set; }  
        public Child2 child2 { get; set; }  
    }  
  
    //public class Parent               ->the class has already defined in the ef, you could remove it  
    //{  
    //    public int Id { get; set; }  
    //    public string Title { get; set; }  
    //    public string description { get; set; }  
    //}  
  
    public class Root  
    {  
        public List<Parent> Parents { get; set; }  
        public Children Children { get; set; }  
    }  

Result of Deserialize:

Result of json:

My Database:

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.