group table by foreign key table in mvc5 using linq

محمد صلاح الدين

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 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> urn = new Dictionary<string, List>();
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();
}
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>
"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)
{
"17" > @key
@foreach (The_Hospital_Project.Models.UserRange item1 in Model[key])
{
"height: 57px;" >@item1.AgeRange
}
@foreach (The_Hospital_Project.Models.UserRange item1 in Model[key])
{
@item1.Gender
}
@foreach (The_Hospital_Project.Models.UserRange item1 in Model[key])
{
@item1.Count
}
}

The table simply collects all the records every month.
What I want is to sort the records by type of disease
each month , for So far, 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 .

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 this Painted image I hope explain what I want It's really what I want

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

محمد صلاح الدين7y ago
i tried that many time but i can't fixed with my code look
public ActionResult ShowPation()

        {

            var query1 = from t in db.Pations.Include(b => b.Type_disease).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_disease.diseaseName

                         group new { t.Age, t.Sex, Agerange } by new { t.DateWared.Year, t.DateWared.Month, Agerange, Sex, t.Type_diseaseId.diseaseName } 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, g.Key.diseaseName, };



            var query3 = from i in query2

                         group i by new { i.Year, i.Month, i.diseaseName} into g

                         select g;



            Dictionary<string, List> urn = new Dictionary<string, List>();

            foreach (var item in query3)

            {

                foreach (var item1 in item)

                {

                    if (!urn.ContainsKey(item.Key.Month + "/" + item.Key.Year + item.Key.diseaseName))

                    {

                        urn[item.Key.Month + "/" + item.Key.Year + item.Key.diseaseName] = new List();

                    }

                    urn[item.Key.Month + "/" + item.Key.Year + item.Key.diseaseName].Add(new UserRange { Count = item1.mycount, AgeRange = item1.Agerange, Gender = item1.Sex });



                }

                urn[item.Key.Month + "/" + item.Key.Year + item.Key.diseaseName] = urn[item.Key.Month + "/" + item.Key.Year + item.Key.NameService].OrderByDescending(i => i.AgeRange).ToList();//sort the data according to Agerange

                var boysTotal = new UserRange() { Count = item.Where(c => c.Sex == "boys").Sum(c => c.mycount), AgeRange = "sum boys", Gender = "boys" };

                var girlsTotal = new UserRange() { Count = item.Where(c => c.Sex == "girls").Sum(c => c.mycount), AgeRange = "sum girls", Gender = "girls" };

                urn[item.Key.Month + "/" + item.Key.Year + item.Key.diseaseName].Add(boysTotal);

                urn[item.Key.Month + "/" + item.Key.Year + item.Key.diseaseName].Add(girlsTotal);

            }

            return View(urn);

        }
and when run this it look like this

test1 and test2 with titel month and that not what i wan't

i needed I want the tables to be test 1 and test 2 within

the same month not every table on every month
Salman Beg7y ago
Hi.
To retrieve the data by using foreign key using linq, you need to use Include() to fetch the data.
Follow this link to retrieve your data,
https://stackoverflow.com/questions/29090622/linq-foreign-key-select
https://entityframework.net/knowledge-base/29090622/linq-foreign-key-select
Thanks.