检查用户是否使用实体框架提交了每日用户

Question

我设置了以下场景，需要返回当天未提交条目的用户。

以下是正在使用的样本数据

var entries = new List<Entry> {
    new Entry { UserId = "fce465f6-089d-4416-a631-7b8201c759d6", Name = "Bugs Bunny", Email = "[e-mail]", CreatedDate = DateTime.Now.AddDays(-1) },
    new Entry { UserId = "1b77f86a-4aad-4788-aa0c-791a7b734181", Name = "Daffy Duck", Email = "[e-mail]", CreatedDate = DateTime.Now.AddDays(-1) },
    new Entry { UserId = "b293bc9d-7626-4c66-888b-e45daebf8322", Name = "Yosemite Sam", Email = "[e-mail]", CreatedDate = DateTime.Now.AddDays(-1) }
    new Entry { UserId = "ff8321fa-b241-456a-b618-826fdd75aaf2", Name = "Pepe Le Pew", Email = "[e-mail]", CreatedDate = DateTime.Now.AddDays(-1) },
    new Entry { UserId = "fce465f6-089d-4416-a631-7b8201c759d6", Name = "Bugs Bunny", Email = "[e-mail]", CreatedDate = DateTime.Now.AddDays(0) },
    new Entry { UserId = "1b77f86a-4aad-4788-aa0c-791a7b734181", Name = "Daffy Duck", Email = "[e-mail]", CreatedDate = DateTime.Now.AddDays(0) },
    new Entry { UserId = "b293bc9d-7626-4c66-888b-e45daebf8322", Name = "Yosemite Sam", Email = "[e-mail]", CreatedDate = DateTime.Now.AddDays(0) }
};
entries.ForEach(e => context.Entries.Add(e));
context.SaveChanges();

在这种情况下，我希望返回一条记录，因为当天没有Pepe Le Pew的条目。

我正在使用以下片段，但我很难理解Where语句。

var entries = db.Entries
    .GroupBy(e => e.UserId)
    .Select(g => g.OrderByDescending(e => e.CreatedDate)
        .Where(e =>
            DbFunctions.TruncateTime(e.CreatedDate) < currentDay.Date
            && DbFunctions.TruncateTime(e.CreatedDate) != currentDay.Date)
        .FirstOrDefault())
    .ToList();

有什么想法吗？

更新

以下解决方案获得@DavidG的巨大赞誉

var entries = db.Entries
    .Where(e => DbFunctions.TruncateTime(e.CreatedDate) < currentDay.Date)
    .Select(e => e.UserId)
    .Except(db.Entries.Where(e => DbFunctions.TruncateTime(e.CreatedDate) == currentDay.Date).Select(e => e.UserId))
    .ToList();

Answer 1

您需要从所有唯一用户的列表开始，例如：

var allUsers = entries
    .GroupBy(u => new { u.UserId })
    .Select(u => new u.Key.UserId);

然后你有一个查询来获取今天提交了一个条目的所有用户：

var entriesToday = db.Entries
    .Where(e => DbFunctions.TruncateTime(e.CreatedDate) == currentDay.Date)
    .GroupBy(e => e.UserId)
    .Select(g => g.Key);

现在，使用Except组合它们，方法是进行一个大查询：

var entries = db.Entries
    .GroupBy(u => new { u.UserId })
    .Select(u => u.Key.UserId)
    .Except(db.Entries
                .Where(e => DbFunctions.TruncateTime(e.CreatedDate) == currentDay.Date)
                .GroupBy(e => e.UserId)
                .Select(g => g.Key);

或者，因为这将使用延迟执行，您可以通过使用前两个查询的结果来简化它：

var entries = allUsers.Except(entriesToday);