实体框架条件选择

Question

Id1	Id2	Date	State
1	1	2022-03-14	Failed
1	1	2022-03-15	Success

鉴于上面的数据库表，我正在尝试编写 EF 查询，其中我们显示每个 Id Id2 组合的最新记录，但失败将永远保留在列表中。只有上周的成功才会被包括在内。

我认为我正在努力解决的部分是，如果给定的 Id Id2 组合存在失败，但未来会成功，则不应返回失败。

关于从哪里开始有什么建议吗？上一次尝试尝试执行某项操作

.Where(x => x.state == 'Failed' || x.CreatedOn > DateTime.Now.AddDays(-7)

，但 10 天前失败，随后 9 天前成功，已列出。 （这种情况应该不返回任何内容，因为成功已经在一周前了）

基本上所有失败，除了后来成功的 UNION 与过去 2 天的成功。 这可能吗？我从来没有在两个表之间的链接上花费这么长时间。

Answer 1

首先一个问题：如果最近的状态是失败，是否只想报告自上次成功以来的失败？换句话说，对于序列{失败，失败，成功，失败，失败}，您只想报告最后两个失败吗？我假设，是的。

在我看来，您首先需要按 Id2 进行分组，并为每个组计算两件事：

1. 最新状态是什么。
2. 最近一次成功的日期是哪一天？

由此，您可以选择该组的成员，其中：

1. LatestState =“Success”且 Date = LateSuccessDate，或
2. State =“Failed”且（LatestSuccessDate 为 null 或 Date > LateSuccessDate）

类似

Data
    .Where(r => r.CreatedOn >= DateTime.Now.AddDays(-7))
    .GroupBy(r => r.Id2)
    .SelectMany(grp => new {
        var latestState = grp
            .OrderByDescending(r => r.CreatedOn)
            .Select(r => r.State)
            .First(),
        var latestSuccessDate = grp
            .OrderByDescending(r => r.CreatedOn)
            .Where(r => r.State = 'Success')
            .Select(r => r.CreatedOn)
            .FirstOrDefault();
        return grp
            .Where(r =>
                (latestState == "Success" && r.CreatedOn == latestSuccessDate)
                || (r.State == "Failure" && (latestSuccessDate == null || r.CreatedOn > latestSuccessDate))
            );
    });

免责声明的内容 - 以上未选中，并且语法上可能不是100%正确，但传达了我的想法。我确信还有优化的空间，例如预先订购每个组的成员。

我希望上面的代码能够完成内存中的大部分工作。我不确定是否可以将其编码为可转换为服务器端 SQL 查询的形式。比我更有技能的人可能会提供解决方案。目标是生成如下内容：

SELECT D.*
FROM (
    -- Select latest state per group
) LS
LEFT JOIN (
    -- Select the latest success date per group
) LSD
    ON LSD.Id2 = LS.Id2
JOIN DATA D
    ON D.Id2 = LS.Id2
WHERE -- Other conditions 

Answer 2

我不确定这是否可以转换为 EF 查询，但我想我会发布它以防万一（或者万一它可以提供一些灵感）。这是一种 Linq to Objects 方法，应该会产生所需的输出。

如果您的类定义如下：

public class Item
{
    public int Id1 { get; set; }
    public int Id2 { get; set; }
    public DateTime Date { get; set; }
    public State State { get; set; }
}

public enum State
{
    Undefined,
    Success,
    Failed
}

并且您的项目存储在 Item 集合中，您可以：

1. 项目进行分组
2. 按 Id1 和 Id2对 在每个组中，选择具有最新 Date 值的一项（使用 .MaxBy())
   • ( .MaxBy( * ) 也可以替换为 .OrderByDescending( * ).First() )
3. 过滤现在不同的Id1 -and-Id2 项目集合仅保留 State = Failed 或不少于一周的项目

对于项目集合 List; items，它可以按如下方式实现：

List<Item> result = items
    .GroupBy(item => ( item.Id1, item.Id2 ))
    .Select(gr => gr.MaxBy(item => item.Date))
    .Where(item =>
          item.State == State.Failed ||
          item.Date > DateTime.Now.AddDays(-7))
    .ToList();

示例小提琴此处。