locked
group table by foreign key table in mvc5 using linq RRS feed

  • Question

  • User-1892044535 posted

    hi How are you guys? I need some help Let me explain to you in the first. I have a table with a group of patients per month that means it's a month data compilation process I want to collect patient data per month according to type of disease i mean a total number of patients per month, depending on the type of disease I tried to collect the data by month but I can't collect the data by type of disease here I need your help

    my pation table like this 

    {
        public class Pation
        {
            [Key]
            public int Id { get; set; }
            public string PationName { get; set; }
            public int Age { get; set; }
            public Sex Sex { get; set; }
            public DateTime DateWared { get; set; } = DateTime.UtcNow.AddHours(3);
            public int Type_diseaseId { get; set; }
            [ForeignKey("Type_diseaseId")]
            public virtual Type_disease Type_diseases { get; set; }
            public ApplicationUser User { get; set; }
        }
        public enum Sex
        {
            boys,
            girls,
        }
    }

    type of disease model which is the foreign key of pation which is i want to group by  it's like this

    {
        public class Type_disease
        {
            [Key]
            public int Id { get; set; }
            public string Namedisease { get; set; }
            public virtual ICollection<Pation> Pations { get; set; }
      
        }
    }

    i grouped the pation by age an sum values in the model i called 

    UserRing and it's like this

    {
        public class UserRange
        {
            public string AgeRange { get; set; }
            public int Count { get; set; }
            public string Gender { get; internal set; }
            public string grilsTotal { get; internal set; }
            public string boysTotal { get; internal set; }
            public string Type_d { get; internal set; }
        }
         
    }

    and i add actionResult named ShowPation to group by the result of pation like this

    public ActionResult ShowPation()
         {
             var query1 = from t in db.Pations()
                          let Agerange =
                           (
                             t.Age >= (0) && t.Age < (1) ? "Under year" :
                             t.Age >= (1) && t.Age < (5) ? "1 _ 4" :
                             t.Age >= (5) && t.Age < (15) ? "5 _ 14" :
                             t.Age >= (15) && t.Age < (25) ? "15 _ 24" :
                             t.Age >= (25) && t.Age < (45) ? "24 _ 44" :
                             "over 45"
                           )
                          let Sex = (t.Sex == 0 ? "boys" : "girls")
                          group new { t.Age, t.Sex, Agerange } by new { t.DateWared.Year, t.DateWared.Month, Agerange, Sex } into g
                          select g;
      
                      var query2 = from g in query1
                          select new { mycount = g.Count(), g.Key.Year, g.Key.Month, g.Key.Agerange, g.Key.Sex };
      
                     var query3 = from i in query2
                          group i by new { i.Year, i.Month} into g
                          select g;
      
             Dictionary<string, List<UserRange>> urn = new Dictionary<string, List<UserRange>>();
             foreach (var item in query3)
             {
                 foreach (var item1 in item)
                 {
                     if (!urn.ContainsKey(item.Key.Month + "/" + item.Key.Year))
                     {
                         urn[item.Key.Month + "/" + item.Key.Year] = new List<UserRange>();
                     }
                     urn[item.Key.Month + "/" + item.Key.Year].Add(new UserRange { Count = item1.mycount, AgeRange = item1.Agerange, Gender = item1.Sex });
      
                 }
                 urn[item.Key.Month + "/" + item.Key.Year] = urn[item.Key.Month + "/" + item.Key.Year].OrderByDescending(i => i.AgeRange).ToList();//sort the data according to Agerange
             }
             return View(urn);
         }

    with view like this

    @model  Dictionary<string, List<The_Hospital_Project.Models.UserRange>>
            <table border="1" class="table table-responsive table-bordered table-hover table-striped " style="height: 145px;text-align: center; border: solid 1px;border-radius: 5px;direction: rtl;">
                @foreach (string key in Model.Keys)
                {
                 <tr>
                    <td colspan="17"> <center>@key</center></td>
                 </tr>
                   <tr>                  
                   
                       @foreach (The_Hospital_Project.Models.UserRange item1 in Model[key])
                       {
                           <td style="height: 57px;">@item1.AgeRange</td>
                       }
                   </tr>
                   <tr>             
                     
                       @foreach (The_Hospital_Project.Models.UserRange item1 in Model[key])
                       {
                           <td>@item1.Gender</td>
                       }
                   </tr>
                <tr>
                
                    @foreach (The_Hospital_Project.Models.UserRange item1 in Model[key])
                    {
                        <td>@item1.Count</td>
                    }
                </tr>
     
                }
            </table>

    The table simply collects all the records every month.
    What I want is to sort the records by type of disease
    each month , 
    So far, the code works well to collect data by month

    What I want is to collect data by disease within each month depending on the type of disease model which is

    the foreign key of pation  to be the result like in each month 

    here

    how can i do this guys i tried so hard but i can't do this please guys help me

    Saturday, January 19, 2019 5:47 AM

All replies

  • User1724605321 posted

    Hi mas mas ,

    What I want is to collect data by disease within each month depending on the type of disease model which is

    What do you mean by `type of disease model which is` , you can join the table in your linq query and add where condition in query . Please explain more about that and fix the image link .

    Best Regards,

    Nan Yu 

    Monday, January 21, 2019 6:06 AM
  • User-1892044535 posted

    hi mr.Nan Yu Thank you for responding to I appreciate 

    this Painted image I hope you explain what I want It's really what I want

    here

    The code I have works well and collects records every month but I can not sort by type of diseases within each month that's all

    Monday, January 21, 2019 12:43 PM
  • User1724605321 posted

    Hi mas mas,

    You can try to join that table and query from the beginning query ,something like :

    var query = from c in db.Categories
             join p in db.Products
             on c.CategoryID equals p.CategoryID
             group new {c, p} by new {c.CategoryName} into g
             select new 
             {
                  g.Key.CategoryName,
                  SumPrice = (decimal?)g.Sum(pt=>pt.p.UnitPrice),
                  Count = g.Select(x=>x.c.CategoryID).Distinct().Count()
             };

    Best Regards,

    Nan Yu

    Wednesday, January 23, 2019 2:09 AM