如何在Entity Framework中编写关联查询？含SQL转EF Core及规范模式需求

你的SQL逻辑是找出每个Platform下状态为Complete且UpdateDate为该平台最新的Release记录，以下是几种对应的EF Core实现方式：

方式一：直接对应原SQL的子查询写法 #

这是最贴近原SQL结构的LINQ实现，EF Core会将其转换为等价的SQL语句：

var latestCompleteReleases = await dbContext.Releases
    .Where(r1 => 
        r1.Status == "Complete" &&
        r1.UpdateDate == dbContext.Releases
            .Where(r2 => r2.Platform == r1.Platform && r2.Status == "Complete")
            .Max(r2 => r2.UpdateDate))
    .ToListAsync();

方式二：窗口函数（ROW_NUMBER）实现 #

对于大数据量场景，窗口函数的性能通常更优（EF Core 3.0+支持）：

// 先给每个Platform下的Complete记录按UpdateDate降序排名
var rankedReleases = dbContext.Releases
    .Where(r => r.Status == "Complete")
    .Select(r => new
    {
        Release = r,
        RowRank = EF.Functions.RowNumber()
            .Over(partitionBy: r.Platform, orderBy: r.UpdateDate descending)
    });

// 筛选出每个Platform下排名第一的记录（即最新的）
var latestCompleteReleases = await rankedReleases
    .Where(x => x.RowRank == 1)
    .Select(x => x.Release)
    .ToListAsync();

规范模式用于封装可复用的查询逻辑，以下是针对该需求的实现：

1. 基于第三方库（Ardalis.Specification）的实现 #

这是社区常用的规范模式库，使用起来更便捷：

using Ardalis.Specification;

public class LatestCompleteReleaseByPlatformSpec : Specification<Release>
{
    public LatestCompleteReleaseByPlatformSpec()
    {
        // 基础过滤：只包含状态为Complete的记录
        Query.Where(r => r.Status == "Complete");

        // 子查询条件：当前记录的UpdateDate是对应Platform下Complete状态的最新日期
        Query.Where(r => 
            r.UpdateDate == Query
                .Where(r2 => r2.Platform == r.Platform && r2.Status == "Complete")
                .Max(r2 => r2.UpdateDate));
    }
}

在Repository中使用该规范：

public class ReleaseRepository : IReleaseRepository
{
    private readonly AppDbContext _dbContext;

    public ReleaseRepository(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<List<Release>> GetLatestCompleteReleasesAsync()
    {
        var spec = new LatestCompleteReleaseByPlatformSpec();
        return await _dbContext.Releases.WithSpecification(spec).ToListAsync();
    }
}

2. 自定义规范（不依赖第三方库） #

如果不想引入第三方库，可自行实现简单的规范接口：

// 定义规范接口
public interface ISpecification<T>
{
    Expression<Func<T, bool>> Criteria { get; }
}

// 实现查询规范
public class LatestCompleteReleaseByPlatformSpec : ISpecification<Release>
{
    private readonly AppDbContext _dbContext;

    public LatestCompleteReleaseByPlatformSpec(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public Expression<Func<Release, bool>> Criteria => 
        r => r.Status == "Complete" &&
             r.UpdateDate == _dbContext.Releases
                 .Where(r2 => r2.Platform == r.Platform && r2.Status == "Complete")
                 .Max(r2 => r2.UpdateDate);
}

// Repository中使用规范
public async Task<List<Release>> GetLatestCompleteReleasesAsync()
{
    var spec = new LatestCompleteReleaseByPlatformSpec(_dbContext);
    return await _dbContext.Releases.Where(spec.Criteria).ToListAsync();
}

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