c# – Linq查询过去7天的日期的不同值,运行查询时抛出异常

发布时间：2020-12-16 01:52:04 所属栏目：百科来源：网络整理

导读：我有一个表Employee,我通过以下方式检索Id和Name字段： var users = context.Employees.ToList() .Select(employee = new KeyValuePairint,string(employee.Id,employee.Name)); 那部分工作正常,我的问题是有另一个表考勤设置外键并且有一个字段LoginDate是

我有一个表Employee,我通过以下方式检索Id和Name字段：

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

那部分工作正常,我的问题是有另一个表考勤设置外键并且有一个字段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.

解决方法

您可以在单个查询中执行所有操作：

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,FROM [dbo].[Attendances] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[Id]
        )  AS [Distinct1]) AS [C1]
FROM [dbo].[Employees] AS [Extent1]

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!