none
How to group data having multiple joins? RRS feed

  • Question

  • I am developing an ASP.Net application based on MVC pattern using Entity Framework.

    I have the following tables:

    1. Case (Id, CourtId, CaseNo)
    2. Court(Id, HeadCourtId, CourtName)
    3. HeadCourt(Id, Name)
    4. Status(Id, CaseId, Date, Disposed)

    What I want is to display disposed-off Cases under the selected Head Court.

    >>Controller

    public ActionResult ShowDisposed(int? HId, string hCourt)
        {
            var initialQuery = from d in db.Status.Where(x => !x.Dispose.Contains("P"))
                               select d;
    
            var finalQuery =
    
                   (from p in initialQuery
                    join c in db.Cases on p.CaseId equals c.Id
                    join cr in db.Courts on c.ourtId equals cr.Id
                    join h in db.HeadCourts on cr.HeadCourtId equals HId
                    orderby p.Date descending
                    select new
                    {
                        CaseId = c.fldCaseId,
                        CaseNo = c.fldCaseNo,
                        Court = cr.fldName,
                        Stage = db.tblStatus.Where(x => x.fldCaseId.Equals(c.fldCaseId)).OrderByDescending(x => x.fldDate).Select(x => x.fldStage).FirstOrDefault(),
    
                    }).ToList();
                
    
            var result = finalQuery.ToList().Select(t => new vmCourtPad
            {
                CaseId =t.CaseId,
                CaseNo = t.CaseNo,
                Court = t.Court,
                Stage = t.Stage
    
            }).ToList();
    
           
    
    
            ViewBag.Head = hCourt;
    
            return View(result);
    
        }

    >> The ViewModel

    namespace CCIS.ViewModel
    {
        public class vmCourtPad
        {
           
            public string CaseNo { get; set; }
            public string Court { get; set; }
            public int CaseId { get; set; }
         
            
        }
    }

    The above query retrieves the records perfectly but multiple times. For instance,

    Instead of displaying records:

    Head Court: Supreme Court

    1. Case 1
    2. Case 3

    I displays as under:

    Head Court: Supreme Court

    1. Case 1
    2. Case 1
    3. Case 1
    4. Case 1
    5. Case 1
    6. Case 3
    7. Case 3
    8. Case 3
    9. Case 3
    10. Case 3

    Please help solve the problem.

    Regards,

    Arun

    Thursday, November 3, 2016 11:56 AM

All replies

  • It's helpful if you give the complete data structure, but I'm guessing from the tables that there are one-to-many relationships from Case->Court, Court->HeadCourt, and Status->Case. In that case the multiple rows per case seem to be due to multiple status rows per case. I'm guessing you need a more restrictive query for the single status row you want to include instead of db.Status.Where(x => !x.Dispose.Contains("P")). You can verify that by including some status attributes in your output. It looks like you want the most recent case status row Not having Dispose containing P, so include that logic in the initial query instead of in the lookup in the second query.


    Paul

    Thursday, November 3, 2016 12:34 PM
  • Dear Paul,

    Thanks for your reply. Let me share with you that the retrieved records get multiplied equal to the number of rows in the table HeadCourt. At present there are 6 records in this table and I have observed that the query returns six same records for each Case. So I guess it is not because of multiple status rows per case.

    Further, the relationship between the tables is like a Headcourt can have many Courts and each court can have many Cases while a Case can have many Status.

    Therefore, please suggest how to fine tune this query in order to eliminate the records from getting multiplied?

    Warm regards,

    Arun. 

    Thursday, November 3, 2016 2:12 PM
  • Not sure if you copied and pasted the original query code, but the join you show from Court to Headcourt has a typo:

    join h in db.HeadCourts on cr.HeadCourtId equals HId

    should have a . separating the H from Id:

    join h in db.HeadCourts on cr.HeadCourtId equals H.Id

    If that join condition is not effective you would get a result row for every Headcourt. If that's not the problem, then please provide the complete sql schema, including the primary and foreign key constraints, because the design you show suggests a single court per case and a single headcourt per court.


    Paul

    Thursday, November 3, 2016 4:26 PM
  • Hi ArunKhatri,

    Do you resolve the issue. If you resolve the issue, could you please mark the helpful as answer. it will be beneficial to other community.

    If the issue still exist, could you please provide a simple project via OneDrive, we'll reproduce your issue on our side and try to find a solution to resolve it.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 11, 2016 9:17 AM
    Moderator