除手动事件外，EF Core追踪另一实体最新行的替代方案咨询

一、物化视图方案（完全可行，EF Core可适配） #

虽然EF Core没有原生支持物化视图，但可以通过手动配置实现：

1. 创建物化视图：直接在数据库中执行SQL创建，不同数据库语法略有差异，以PostgreSQL为例：

CREATE MATERIALIZED VIEW TwoRecentUsers AS
SELECT 
    ROW_NUMBER() OVER () AS ID,
    SchoolId,
    -- 获取最新用户的信息
    FIRST_VALUE(UserName) OVER (PARTITION BY SchoolId ORDER BY CreatedAt DESC) AS MostRecentUserName,
    FIRST_VALUE(CreatedAt) OVER (PARTITION BY SchoolId ORDER BY CreatedAt DESC) AS MostRecentCreatedAt,
    -- 获取次新用户的信息
    NTH_VALUE(UserName, 2) OVER (PARTITION BY SchoolId ORDER BY CreatedAt DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS SecondMostRecentUserName,
    NTH_VALUE(CreatedAt, 2) OVER (PARTITION BY SchoolId ORDER BY CreatedAt DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS SecondMostRecentCreatedAt
FROM "User"
GROUP BY SchoolId;

2. EF Core映射配置：将物化视图当作只读实体映射到DbContext：

public DbSet<TwoRecentUsers> TwoRecentUsers { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TwoRecentUsers>(entity =>
    {
        entity.HasKey(e => e.ID);
        entity.ToView("TwoRecentUsers"); // 关联到物化视图
        entity.Property(e => e.SchoolId).IsRequired();
        entity.Property(e => e.MostRecentUserName).HasMaxLength(50);
        // 其他字段按实际需求配置映射
    });
}

3. 刷新策略：
   • 定时刷新：用数据库定时任务（如PostgreSQL的pg_cron、SQL Server的Agent Job）定期执行REFRESH MATERIALIZED VIEW TwoRecentUsers;
   • 触发式刷新：在User实体创建并保存后，手动执行SQL刷新视图：

     await _context.Database.ExecuteSqlRawAsync("REFRESH MATERIALIZED VIEW TwoRecentUsers");
     

二、数据库触发器方案 #

直接在数据库层写触发器，当User新增时自动更新TwoRecentUsers表，完全无需EF Core做额外逻辑：

1. 先创建TwoRecentUsers实体对应的表：包含ID、SchoolId，以及两个用户的主键字段（如MostRecentCreatedAt、SecondMostRecentCreatedAt）
2. 编写INSERT触发器（以PostgreSQL为例）：

CREATE OR REPLACE FUNCTION update_two_recent_users()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新已有学校的记录：把旧的最新用户挤到第二位，新用户成为最新
    WITH current_recent AS (
        SELECT MostRecentCreatedAt, SecondMostRecentCreatedAt
        FROM TwoRecentUsers
        WHERE SchoolId = NEW.SchoolId
    )
    UPDATE TwoRecentUsers
    SET 
        SecondMostRecentCreatedAt = current_recent.MostRecentCreatedAt,
        MostRecentCreatedAt = NEW.CreatedAt
    WHERE SchoolId = NEW.SchoolId;

    -- 若该学校无记录，插入新行
    IF NOT EXISTS (SELECT 1 FROM TwoRecentUsers WHERE SchoolId = NEW.SchoolId) THEN
        INSERT INTO TwoRecentUsers (SchoolId, MostRecentCreatedAt)
        VALUES (NEW.SchoolId, NEW.CreatedAt);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_after_user_insert
AFTER INSERT ON "User"
FOR EACH ROW EXECUTE FUNCTION update_two_recent_users();

EF Core只需正常映射TwoRecentUsers实体即可，全程无需领域事件或额外代码。

三、普通查询视图（实时但性能略逊） #

如果不需要持久化存储，只是需要实时查询每个学校的最新两个用户，可以创建普通视图：

CREATE VIEW TwoRecentUsers AS
SELECT 
    SchoolId,
    MAX(CASE WHEN rn = 1 THEN UserName END) AS MostRecentUserName,
    MAX(CASE WHEN rn = 1 THEN CreatedAt END) AS MostRecentCreatedAt,
    MAX(CASE WHEN rn = 2 THEN UserName END) AS SecondMostRecentUserName,
    MAX(CASE WHEN rn = 2 THEN CreatedAt END) AS SecondMostRecentCreatedAt
FROM (
    SELECT 
        SchoolId,
        UserName,
        CreatedAt,
        ROW_NUMBER() OVER (PARTITION BY SchoolId ORDER BY CreatedAt DESC) AS rn
    FROM "User"
) t
WHERE rn <= 2
GROUP BY SchoolId;

EF Core映射方式和物化视图一致，数据实时但大表下查询性能不如物化视图。

四、后台定时任务 #

对实时性要求不高的场景，可以写定时服务（如Hangfire、Quartz），定期执行查询并更新TwoRecentUsers表。实现简单，无需修改数据库结构或触发器。

内容的提问来源于stack exchange，提问作者delydelydely1