如何在LINQ中分组

本文关键字：LINQ | 更新日期: 2023-09-27 18:25:35

我需要返回特定用户每日预约的最后30天，并检查用户是否每天至少预约8小时。

在sql中，我可以使用以下命令：

select IDAppointment,IDUser, SUM(DurationInHours) from Note where AppointmentDate > *lastmonth and IDUser = @userID group by IDUser,IDAppointment,AppointmentDate

然后我得到结果并验证DurationInHours（双重类型）。

使用LINQ可以做到这一点吗？获取上个月用户预约的列表，并每天对其进行验证

谢谢！

如何在LINQ中分组

这应该是大致存在的，尽管这不是在IDE中，我一时兴起。

var result = context.Notes
                    .Where(n => [Your where clause])
                    .GroupBy(n => new { n.IDUser, n.IDAppointment, n.AppointmentDate})
                    .Select(g => new {
                                   g.Key.IDAppointment,
                                   g.Key.IDUser,
                                   g.Sum(n => n.DurationInHours)});

更新：

作为参考，你的where子句将是这样的。。。（再次从我的头顶）

DateTime lastMonth = DateTime.Today.AddMonths(-1);
int userId = 1 // TODO: FIX
var result = context.Notes.Where(n => n.AppointmentDate > lastMonth
                                   && n.IDUser = userId)

导致…

DateTime lastMonth = DateTime.Today.AddMonths(-1);
int userId = 1 // TODO: FIX
var result = context.Notes
                    .Where(n => n.AppointmentDate > lastMonth
                             && n.IDUser = userId)
                    .GroupBy(n => new { n.IDUser, n.IDAppointment, n.AppointmentDate})
                    .Select(g => new {
                                   g.Key.IDAppointment,
                                   g.Key.IDUser,
                                   g.Sum(n => n.DurationInHours)});

这是我测试过的一个解决方案。

DateTime lastMonth = DateTime.Today.AddMonths(-1);
int selectedUserId = 2; 
var notes = new List<Note>(
    new Note[] {
        new Note() { 
            AppointmentDate = new DateTime(2013,7,30){}, 
            IDAppointment = 1, IDUser = 1, DurationInHours = 1
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,30){}, 
            IDAppointment = 1, IDUser = 1, DurationInHours = 2
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,30){}, 
            IDAppointment = 1, IDUser = 1, DurationInHours = 3
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,28){}, 
            IDAppointment = 2, IDUser =  2, DurationInHours = 2
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,28){}, 
            IDAppointment = 2, IDUser =  2, DurationInHours = 3
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,27){}, 
            IDAppointment = 2, IDUser =  2, DurationI nHours = 4
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,26){}, 
            IDAppointment = 3, IDUser =  3, DurationInHours = 3
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,25){}, 
            IDAppointment = 3, IDUser =  3, DurationInHours = 4
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,24){}, 
            IDAppointment = 3, IDUser =  3, DurationInHours = 5
        }
    }
);
var results = from n in notes
              group n by new {n.IDUser, n.IDAppointment, n.AppointmentDate}
              into g
              where g.Key.AppointmentDate > lastMonth && 
                    g.Key.IDUser == selectedUserId
              select new {
                  g.Key.IDAppointment, 
                  g.Key.IDUser, 
                  TotalHours = g.Sum(n => n.DurationInHours)
              };

求和属性需要显式给定一个名称（即TotalHours），否则会出现错误CS0746:无效的匿名类型成员声明符。匿名类型成员必须使用成员分配、简单名称或成员访问权限声明

相关文章：