locked
how the display the table group by foreign key in mvc5 RRS feed

  • Question

  • User-1892044535 posted

    hi guys i have a table name Patient and in this table i am using foreign key to another table name Type_Service i want to group the table by each Type_Service
    and i need some help

    here is my code for Type_Service model

    namespace The_Hospital_Project.Models
    {
    public class Type_Service
    {
    [Key]
    public int Id { get; set; }
    public string NameService { get; set; }
    public virtual ICollection<Pation> Pations { get; set; }

    }
    }

    **and here is my pation model**

    namespace The_Hospital_Project.Models
    {
    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_ServiceId { get; set; }
    [ForeignKey("Type_ServiceId")]
    public virtual Type_Service Type_Services { get; set; }
    public ApplicationUser User { get; set; }

    }
    }

    **here is my controller**

    public ActionResult Mster()
    {
    var query = (from t in db.Pations
    let Agerange =
    (
    t.Age >= 0 && t.Age < 1 ? "Age Under 1 year" :
    t.Age >= 1 && t.Age < 5 ? "Age from 1 to 4" :
    t.Age >= 5 && t.Age < 15 ? "Age from 5 to 14" :
    t.Age >= 15 && t.Age < 25 ? "Age from 15 to 24" :
    t.Age >= 25 && t.Age < 45 ? "Age from 25 to 46" :
    "age over 47+"
    )
    let Sex = (t.Sex == 0 ? "boys" : "girls")
    group t by new { Agerange, Sex } into g
    select new UserRange { AgeRange = g.Key.Agerange, Gender = g.Key.Sex, Count = g.Count() }).ToList();
    var boysTotal = new UserRange() { Gender = "boys", AgeRange = "boys sum", Count = query.Where(c => c.Gender == "boys").Sum(c => c.Count) };
    var grilsTotal = new UserRange() { Gender = "girls", AgeRange = "girls sum", Count = query.Where(c => c.Gender == "girls").Sum(c => c.Count) };
    query.Add(boysTotal);
    query.Add(grilsTotal);
    ViewBag.UserData = query;
    return View(db.Pations);
    }

    I group this and work good but i wont to be like thie

    here


    **and this is a UserRange model view**


    namespace The_Hospital_Project.Models
    {
    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; }
    }

    }

    please guys help me how group all this table by Type_Service that's all

    Monday, December 24, 2018 4:19 PM

Answers

  • User1520731567 posted

    Hi mas mas,

    According to your model,

    public int Type_ServiceId { get; set; }
    [ForeignKey("Type_ServiceId")]

    I suggest you should add primary key named Type_ServiceId  in Type_Service,like:

    public class Type_Service
    {
    [Key]
    //public int Id { get; set; }
    public int Type_ServiceId  { get; set; } public string NameService { get; set; } public virtual ICollection<Pation> Pations { get; set; } }

    And in database,you need to add related data value about Type_ServiceId  in Type_Service table and Pation table so that  these two tables can be associated.

    And then:

    modify the class UserRange, like:

    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_S { get; internal set; }
                
            }

    Using Include(eager Loading) to load related data from two tables.Add new field named Type_S which represent NameService.

      var query = (from t in db.Pations.Include(b=>b.Type_Services).ToList()
                             let Agerange =
                             (
                             t.Age >= 0 && t.Age < 1 ? "Age Under 1 year" :
                             t.Age >= 1 && t.Age < 5 ? "Age from 1 to 4" :
                             t.Age >= 5 && t.Age < 15 ? "Age from 5 to 14" :
                             t.Age >= 15 && t.Age < 25 ? "Age from 15 to 24" :
                             t.Age >= 25 && t.Age < 45 ? "Age from 25 to 46" :
                             "age over 47+"
                             )
                             let Sex = (t.Sex == 0 ? "boys" : "girls")
                             let Type_S=t.Type_Services.NameService
                             group t by new { Agerange, Sex, Type_S } into g
                             select new UserRange { AgeRange = g.Key.Agerange, Gender = g.Key.Sex, Type_S = g.Key.Type_S, Count = g.Count() }).ToList();

    Finally,group the result based on Type_S .

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 25, 2018 9:13 AM
  • User1520731567 posted

    Hi mas mas,

    how you doing thank you for helping me man it's work fine but it's not look like my image it's look like this it give me all types of Type_Service  in one table what i need is

    Your source is worry.

    As I said before, you need to group result again,even nesting group in group.

    For example:

    //groupby Type_Services and get sum count based on Gender
    var boysTotal = query.GroupBy(_ => _.Type_S, (key, group) => new { TypeServiceName = key, Count = group.Where(_ => _.Gender == "boys").Sum(_ => _.Count) }).ToList(); var grilsTotal = query.GroupBy(_ => _.Type_S, (key, group) => new { TypeServiceName = key, Count = group.Where(_ => _.Gender == "girls").Sum(_ => _.Count) }).ToList();
    //groupby Type_Services and then groupby AgeRange var list = query.GroupBy(s => s.Type_S, (key, group) => new { TypeSviceName = key, Group = group.ToList().GroupBy(b => b.AgeRange).ToList() });

    Look the picture:

    Boy:

    NameService1:0

    NameService1:2

    Girl:

    NameService1:1

    NameService2:1

    And then you could define some ViewBag to get boysTotalgrilsTotal  and groupby to render view ,sometimes you need to use ViewBag.xxx.xxx format to get value in view.

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 27, 2018 5:42 AM

All replies

  • User1520731567 posted

    Hi mas mas,

    According to your model,

    public int Type_ServiceId { get; set; }
    [ForeignKey("Type_ServiceId")]

    I suggest you should add primary key named Type_ServiceId  in Type_Service,like:

    public class Type_Service
    {
    [Key]
    //public int Id { get; set; }
    public int Type_ServiceId  { get; set; } public string NameService { get; set; } public virtual ICollection<Pation> Pations { get; set; } }

    And in database,you need to add related data value about Type_ServiceId  in Type_Service table and Pation table so that  these two tables can be associated.

    And then:

    modify the class UserRange, like:

    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_S { get; internal set; }
                
            }

    Using Include(eager Loading) to load related data from two tables.Add new field named Type_S which represent NameService.

      var query = (from t in db.Pations.Include(b=>b.Type_Services).ToList()
                             let Agerange =
                             (
                             t.Age >= 0 && t.Age < 1 ? "Age Under 1 year" :
                             t.Age >= 1 && t.Age < 5 ? "Age from 1 to 4" :
                             t.Age >= 5 && t.Age < 15 ? "Age from 5 to 14" :
                             t.Age >= 15 && t.Age < 25 ? "Age from 15 to 24" :
                             t.Age >= 25 && t.Age < 45 ? "Age from 25 to 46" :
                             "age over 47+"
                             )
                             let Sex = (t.Sex == 0 ? "boys" : "girls")
                             let Type_S=t.Type_Services.NameService
                             group t by new { Agerange, Sex, Type_S } into g
                             select new UserRange { AgeRange = g.Key.Agerange, Gender = g.Key.Sex, Type_S = g.Key.Type_S, Count = g.Count() }).ToList();

    Finally,group the result based on Type_S .

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 25, 2018 9:13 AM
  • User-1892044535 posted

    hi mr.Yuki Tao

    how you doing thank you for helping me man it's work fine but it's not look like my image it's look like this it give me all types of Type_Service  in one table what i need is

    by each table 

    here

    and here is my view like this

    <table border="1" class="table table-responsive table-bordered table-hover table-striped "style="height: 145px;text-align: center; border: solid 1px;border-radius: 13px;">

    @foreach (var item in ViewBag.UserData)
    {
    @item.Type_S
    }

    <tr style="height: 60px; background-color: #d2d2d2;">
    @foreach (var item in ViewBag.UserData)
    {
    <th>@item.AgeRange </th>
    }

    </tr>

    <tr>

    @foreach (var item in ViewBag.UserData)
    {
    <th>@item.Gender </th>
    }
    </tr>
    <tr>
    @foreach (var item in ViewBag.UserData)
    {
    <td>@item.Count </td>
    }
    </tr>

    </table>

    Wednesday, December 26, 2018 7:51 PM
  • User-1892044535 posted

    it's not like my image example  man i don't know what i mass but may be the problem  in my view code please help  me  man but the code work fine

    Wednesday, December 26, 2018 7:53 PM
  • User1520731567 posted

    Hi mas mas,

    how you doing thank you for helping me man it's work fine but it's not look like my image it's look like this it give me all types of Type_Service  in one table what i need is

    Your source is worry.

    As I said before, you need to group result again,even nesting group in group.

    For example:

    //groupby Type_Services and get sum count based on Gender
    var boysTotal = query.GroupBy(_ => _.Type_S, (key, group) => new { TypeServiceName = key, Count = group.Where(_ => _.Gender == "boys").Sum(_ => _.Count) }).ToList(); var grilsTotal = query.GroupBy(_ => _.Type_S, (key, group) => new { TypeServiceName = key, Count = group.Where(_ => _.Gender == "girls").Sum(_ => _.Count) }).ToList();
    //groupby Type_Services and then groupby AgeRange var list = query.GroupBy(s => s.Type_S, (key, group) => new { TypeSviceName = key, Group = group.ToList().GroupBy(b => b.AgeRange).ToList() });

    Look the picture:

    Boy:

    NameService1:0

    NameService1:2

    Girl:

    NameService1:1

    NameService2:1

    And then you could define some ViewBag to get boysTotalgrilsTotal  and groupby to render view ,sometimes you need to use ViewBag.xxx.xxx format to get value in view.

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 27, 2018 5:42 AM