LINQ Group通过多列和计数 [英] LINQ GroupBy multiple columns and Count

问题描述

模型类

public class tstsStates
{
    public string Name { get; set; }
    public string title{ get; set; }
    public int jcount { get; set; }
    public int DCount { get; set; }
    public Guid JobId { get; set; }
    public Guid PId { get; set; }
    public Guid JId { get; set; }
    public DateTime Date { get; set; }
}

我的Lambda查询

List<model> list= _context.Table
    .Include(x => x.Table1)
    .Include(x => x.Table2)
    .Include(x => x.Table2)
    .Where(x => Ids.Contains(x.Table4.Id))
    .Select(x => new model
    {
        Name = x.table.FirstName + "" + x.table.LastName,
        Position = x.table.Title,
        JobId = x.table.Id,
        id= x.Id,
        rId= x.Id,
        Date = x.Date

    }).ToList();   //Till here query works absolutely fine.


// How i can group two columns and get the count.

我的问题:

1)我缺少按两列(PId，jbId)分组，进行计数并将其分配回我的模型以将其返回视图的逻辑.

1) I am missing the logic of grouping by two columns (PId, jbId), taking count and assigning it back to my model to return it back to view.

2)我缺少按两列(pid，mydate)分组的逻辑，采用dCount并将其分配回我的模型以将其返回视图.

2) I am missing the logic of grouping by two columns (pid, mydate), taking dCount and assigning it back to my model to return it back to view.

我的要求

jcount:我需要从IEnumerable上面计算不同的JobIds吗?

jcount: I need to take count of Distinct JobIds from above IEnumerable?

dcount::我需要从IEnumerable上面计算不同日期"吗?

dcount: I need to take count of Distinct Date from above IEnumerable?

原因:

推荐答案

据我了解，您想显示JobsCount&每个人的天数，每个人都有不同的标题，在这种情况下，请尝试以下操作:-

As far as I have understood, you want to display the JobsCount & Days Count for each person and each having different set of titles, in that case try this:-

var result = tstPData.GroupBy(x => new { x.PersonnelId, x.Name , x.Position })
                     .Select(x => new 
                                    {
                                        PersonnelId = x.Key.PersonnelId,
                                        Name = x.Key.Name,
                                        Position = x.Key.Position ,
                                        JobCount = x.Count(),
                                        DaysCount = x.Count() 
                                    });

最后，您可以枚举结果并将其分配给您的模型.

Finally you can enumerate over the result and assign to your Model.

更新:

我已经根据您的模型创建了自定义数据对象:-

I have created custom data object based on your Model:-

List<PersonnelStats> persons = new List<PersonnelStats>
            {
                //Same Job for Alex but different date
                new PersonnelStats { Name="Alex", Position="Dev", JobId= new Guid("11223344-5566-7788-99AA-BBCCDDEEFF00"), PersonnelId = new Guid("11223344-5566-7788-99AA-BBCCDDEEFF00"), Date= new DateTime(2015,03,12)},
                new PersonnelStats { Name="Alex", Position="Dev", JobId= new Guid("11223344-5566-7788-99AA-BBCCDDEEFF00"), PersonnelId = new Guid("11223344-5566-7788-99AA-BBCCDDEEFF00"), Date= new DateTime(2015,03,15)},
                //Two jobs for Mary
                new PersonnelStats { Name="Mary", Position="App", JobId= new Guid("11229944-2356-7788-99AA-BBCCDDEEFF00"), PersonnelId = new Guid("11223344-1567-6565-99AA-BBCCDDEEFF00"), Date= new DateTime(2015,03,13)},
                new PersonnelStats { Name="Mary", Position="App", JobId= new Guid("11229944-3445-7788-99AA-BBCCDDEEFF00"), PersonnelId = new Guid("11223344-1567-6565-99AA-BBCCDDEEFF00"), Date= new DateTime(2015,03,13)},
                //Two jobs for Mark with 1 job being at different dates.
                new PersonnelStats { Name="Mark", Position="Test", JobId= new Guid("11223344-7879-2344-99AA-BBCCDDEEFF00"), PersonnelId = new Guid("11223344-2245-4343-99AA-BBCCDDEEFF00"), Date= new DateTime(2015,03,17)},
                new PersonnelStats { Name="Mark", Position="Test", JobId= new Guid("11223344-5522-2344-99AA-BBCCDDEEFF00"), PersonnelId = new Guid("11223344-2245-4343-99AA-BBCCDDEEFF00"), Date= new DateTime(2015,03,17)},
                new PersonnelStats { Name="Mark", Position="Test", JobId= new Guid("11223344-5522-2344-99AA-BBCCDDEEFF00"), PersonnelId = new Guid("11223344-2245-4343-99AA-BBCCDDEEFF00"), Date= new DateTime(2015,03,18)},
            };

这是为我提供所需输出的代码:-

And this is the code which gives me the desired output:-

            var result = persons.GroupBy(x => new { x.PersonnelId, x.Name, x.Position })
                     .Select(x => new
                     {
                         PersonnelId = x.Key.PersonnelId,
                         Name = x.Key.Name,
                         Position = x.Key.Position,
                         JobCount = x.Select(z => z.JobId).Distinct().Count(),
                         DaysCount = x.Select(z => z.Date.Date).Distinct().Count()
                     });

这篇关于LINQ Group通过多列和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！