CODEWITHSUNDEEP
c#entity-frameworklinqasp.net-core-mvc
XamDev
XamDev

Reputation: 3647

Linq to sql query with Parent child relationship

I have following model for posting the question and replies to the question

Model

public partial class LMS_Question
{
    [Key]
    public int ClassDiscussionID { get; set; }
    public int? ParentClassDiscussionID { get; set; }
    public string Discussion { get; set; }
    public string DiscussionTitle { get; set; }
}

When user will post new question the 'ParentClassDiscussionID' will be null and if any reply added to the question then 'ClassDiscussionID' will be updated to 'ParentClassDiscussionID'.

Basically ClassDiscussionID and ParentClassDiscussionID columns have parent-child relationship.

However, I want to show the data like DiscussionTitle,ReplyCount as follows

DiscussionTitle ReplyCount
-------------------------
Question1    2 Reponses
Question2    3 Reponses
Question3    5 Reponses

So, how can I achieve this using linq to sql query ?

Thanks for the help !

Upvotes: 2

Views: 993

Answers (1)

Arash
Arash

Reputation: 885

First of all you need to change your model to something like this

public partial class LMS_ClassDiscussion
    {
        public LMS_ClassDiscussion()
        {
            LMS_ClassDiscussionchild = new List<LMS_ClassDiscussion>();
        }
        public int ClassDiscussionID { get; set; }
        public int? ParentClassDiscussionID { get; set; }
        public string Discussion { get; set; }
        public string DiscussionTitle { get; set; }
        public LMS_ClassDiscussion _LMS_ClassDiscussion { get; set; }
        public List<LMS_ClassDiscussion> LMS_ClassDiscussionchild { get; set; }
    }

then you need method to get all children of parents

 public  List<LMS_ClassDiscussion> GetChildren(IList<LMS_ClassDiscussion> source, int? parentId)
        {
            var children = source.Where(x => x.ParentClassDiscussionID == parentId).ToList();
            //GetChildren is called recursively again for every child found
            //and this process repeats until no childs are found for given node, 
            //in which case an empty list is returned
            children.ForEach(x => x.LMS_ClassDiscussionchild = GetChildren(source, x._LMS_ClassDiscussion.ClassDiscussionID));
            return children.ToList();

        }

call method this way

 var LMS_ClassDiscussions = GetChildren(query, null);
  foreach (var item in LMS_ClassDiscussions)
        {
            Console.WriteLine(item._LMS_ClassDiscussion.ClassDiscussionID + "="+item.LMS_ClassDiscussionchild.Count);
        }

Upvotes: 2

Related Questions