Many To Many Relationship EF 6 Parents Players

NOVATROOP77 266 Reputation points
2022-09-06T16:10:24.03+00:00

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,  
    .....  
  }  
  ]  
}  
Developer technologies | .NET | Other
Developer technologies | C#
{count} votes

1 answer

Sort by: Most helpful
  1. Jack J Jun 25,296 Reputation points
    2022-09-08T07:11:19.957+00:00

    @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:

    239235-image.png

    Result of json:

    238936-image.png

    My Database:

    238904-image.png

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.