c# - Linq 查询最近 7 天的不同日期值，运行查询时抛出异常

Question

我有一个表 Employee，我通过以下方式检索了 Id 和 Name 字段:

var users = context.Employees.ToList()
                   .Select(employee => new KeyValuePair<int, string>(employee.Id,employee.Name)); 

那部分工作正常，我的问题是还有另一个表 Attendance 设置了外键，并且有一个字段 LoginDate，它是 DateTime 值。一个用户可以多次登录，所以我想获得用户在过去 7 天内登录次数的不同值。

foreach (var user in users)
{
    var days = context.Attendances.Where(x => x.Id == user.Key && x.LoginDate.Date > DateTime.Now.AddDays(-7)).Distinct().ToList();
     int count = days.Count();
     _attendanceTable.Rows.Add(user.Key, user.Value, count);
 }

当我运行 Attendance 表的查询时出现异常:

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Answer 1

您可以在一个查询中完成所有操作:

var date = DateTime.Now.AddDays(-7).Date; // I think you need date only here
var query = from e in context.Employees
            join a in context.Attendances on e.Id equals a.Id into g
            select new
            {
                e.Id,
                e.Name,
                Count = g.Where(x => x.LoginDate > date)
                         .GroupBy(x = > new {
                               x.LoginDate.Year,
                               x.LoginDate.Month,
                               x.LoginDate.Day 
                          }).Count()
            };

foreach(var user in query)
   _attendanceTable.Rows.Add(user.Id, user.Name, user.Count);

此外，EntityFramework 不支持 DateTime 的 Date 属性。您应该使用匿名对象按日期部分对出勤进行分组。

生成的 SQL 查询如下所示:

SELECT [Extent1].[Id] AS [Id], 
       [Extent1].[Name] AS [Name], 
    (SELECT 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            DATEPART (year, [Extent2].[LoginDate]) AS [C1], 
            DATEPART (month, [Extent2].[LoginDate]) AS [C2],
            DATEPART (day, [Extent2].[LoginDate]) AS [C2],
            FROM [dbo].[Attendances] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[Id]
        )  AS [Distinct1]) AS [C1]
FROM [dbo].[Employees] AS [Extent1]