在Mvc5中使用EntityFramwork按内部分组 [英] Group by Inside Group by Using EntityFramwork in Mvc5

问题描述

我正在尝试按年龄划分每个男性和女性的人数，如表

Hi guys I am trying I try to bring the count for each male and female by age As in the table here

我能够计算所有年龄，但我不能将年龄归为男孩和女孩.我需要帮助

I was able to calculate all ages but I can not count the ages as boys and girls. I want help, please

这是我在控制器中的代码

here is my code in controller

public ActionResult AllCuont()
{

    var query = (from t in db.Pations
                 let range = (
                              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 44" :
                              ""
                              )
                 group t by range into g


                 select new UserRange { AgeRange = g.Key,  Count = g.Count() }).ToList();
    //add the sum column.
    query.Add(new UserRange() { AgeRange = "Sum",Count = query.Sum(c => c.Count) });

    ViewBag.UserData = query;

    return View(db.Pations);
}

和我这样的情态表情

   namespace app.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 ApplicationUser User { get; set; }

    }
    public enum Sex
    {
        boys,
        girls,
    }
}

这是计算我的价值的方式

   public class UserRange
    {
        public string AgeRange { get; set; }
        public int Count { get; set; }

    }

如何按年龄对男性和女性进行计数

推荐答案

我不清楚您要在图片行中放入什么. Pations?因此，一列可能比另一列具有更多行?您想在最后一列中输入什么内容?

It's not clear to me what you want to put in the rows of your picture. The Pations? So one column could have more rows than another column? And what do you want to put in your last column?

无论如何，您有一个Pations序列(患者?)，并且想要将它们分为年龄范围相等的Pations组.每个年龄段相同的人群都应分为Pations组，且各等分的Sex.

Anyway, you have a sequence of of Pations (Patients?), and you want to divide them into groups of Pations with equal age range. Every group of same age range should be divided into group of Pations with equal Sex.

因此，让我们首先给出每个Pation和年龄范围.出于效率原因，我将您的年龄范围从零到四，然后将年龄范围更改为文本

So let's first give every Pation and age range. For efficiency reasons, I'll number your age ranges from zero to four, later I'll change the age ranges into text

var query = dbContext.Pations                   // from the table of Pations
    .Where(patient => patient.Age < 45)         // keep only the Patiens younger than 45
    .Select(patient => new                      // from every remaining patient,
    {                                           // make one new object
         AgeRange = (patient.Age < 5) ? 0 :
                    (patient.Age < 15) ? 1 :
                    (patient.Age < 25) ? 2 : 3, // with a number 0..3, indicating the age range
         Gender = patient.Sex,                  // a property indicating the gender

         Patient = patient,                     // and the original Patient
    })

我通过将所有元素分组为具有相同年龄范围的患者组来继续查询.由于年龄范围是数字，因此这种分组非常有效.

I continue the query by grouping all element into groups of Patients with equal age range. Because the age range is a number, this grouping is very efficient.

每个组均由相同年龄范围内的患者组成.我将每个组划分为一个男孩子组和一个女孩子组.计算每个子组中的元素数量.

Every group consists of Patients within the same age range. I'll divide every group into a subgroup of Boys and a subgroup of Girls. The number of elements in every subgroup is counted.

    .GroupBy(patient => patient.AgeRange,       // group into groups of equal age range
    (ageRange, patientsWithThisAgeRange) => new // from the common ageRange and all
    {                                           // patients with this ageRange make one new
        AgeRange = ageRange,                   // remember the ageRange

        // put all Boys in this agegroup in a separate subgroup
        BoysGroup = patientsWithThisAgeRange                       
            .Where(patientWithThisAgeRange => patientWithThisAgeRange.Gender == Sex.Boys)
            .ToList(),

         // put all Girls in a separate sub group

        GirlsGroup = patientsWithThisAgeRange    // count number of girls in this group
            .Where(patientWithThisAgeRange => patientWithThisAgeRange.Gender == Sex.Girls)
            .ToList(),
    })

由于ToList，您将拥有Boys以及Boys的数量.如果最终结果中不需要男孩和女孩，而只需要男孩的数量，则将Count替换为Count

Because of the ToList, you'll have the Boys as well as the number of Boys. If you don't need the boys and the girls in your final result, but only the number of boys, replace ToList with Count

最后，将所有数据从数据库管理系统移至本地进程，并将年龄组转换为文本:

Finally, move all data from your database management system to your local process and convert the age group into text:

    .AsEnumerable()
    .Select(group => new
    {
        Description = AgeRangeToText(group.AgeRange),
        NrOfBoys = group.NrOfBoys,
        NrOfGirls = group.NrOfGirls,
    });

您唯一需要做的就是编写一个函数，将ageRanges 0..4转换为正确的文本.

The only thing you'll have to do is to write a function that translates your ageRanges 0..4 into proper texts.

这篇关于在Mvc5中使用EntityFramwork按内部分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！