EF Core子查询关联问题：获取表最新版本记录的解决方案

注意：时态数据特性不可用。

理想生成的SQL（CTE方式） #

WITH cte (Id, Version) AS 
(
    SELECT 
        Id, MAX(Version)
    FROM 
        Table
    GROUP BY 
        Id
)
SELECT a.*
FROM Table a
JOIN cte ON a.Id = cte.Id AND cte.Version = a.Version;

等价替代SQL #

如果CTE方式在EF Core中无法实现，可使用以下等价查询：

SELECT a.*
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Version DESC) rn
    FROM Table a
    WHERE redacted
) a
WHERE a.rn = 1;

问题重现 #

我编写的C# LINQ查询如下：

var query = (from a in context.Test
            join j in (from m in context.Test
                        group m by m.Id
                        into g
                        select new { g.Key, Version = g.Max(x => x.Version) })
            on new { a.Id, a.Version } equals new { Id = j.Key, j.Version } into gj
            select a).ToQueryString();

实际生成的SQL完全缺失了子查询部分：

SELECT [a].[Id], [a].[Version]
FROM [Employer] AS [e]

2024年更新：可行解决方案 #

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

using var context = new MyDbContext();

var query = context.Entities
    .GroupBy(t => t.Id)
    .Select(g => g.OrderByDescending(t => t.Version).FirstOrDefault())
    .ToQueryString();

Console.WriteLine(query);

public class Entity {
    public required int Id { get; set; }
    public required int Version { get; set; }
    public required string Name { get; set; }
}

public class EntityTypeConfiguration : IEntityTypeConfiguration<Entity>
{
    public void Configure(EntityTypeBuilder<Entity> builder)
    {
        builder
            .HasKey(b => new { b.Id, b.Version });

        builder.Property(b => b.Id)
            .ValueGeneratedOnAdd();

        builder
            .Property(b => b.Name)
            .IsRequired();

        builder.Property(b => b.Version)
            .HasDefaultValue(0);
    }
}

public class MyDbContext : DbContext
{
    public DbSet<Entity> Entities { get; set; }

    public MyDbContext() : base() {}

    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) {}

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=tcp:efcore-stackoverflow.database.windows.net,1433;Initial Catalog=efcore-stackoverflow;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=""Active Directory Default"";");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(EntityTypeConfiguration).Assembly);
    }
}

生成的SQL输出 #

SELECT [e3].[Id], [e3].[Version], [e3].[Name]
FROM (
    SELECT [e].[Id]
    FROM [Entities] AS [e]
    GROUP BY [e].[Id]
) AS [e1]
LEFT JOIN (
    SELECT [e2].[Id], [e2].[Version], [e2].[Name]
    FROM (
        SELECT [e0].[Id], [e0].[Version], [e0].[Name], ROW_NUMBER() OVER(PARTITION BY [e0].[Id] ORDER BY [e0].[Version] DESC) AS [row]
        FROM [Entities] AS [e0]
    ) AS [e2]
    WHERE [e2].[row] <= 1
) AS [e3] ON [e1].[Id] = [e3].[Id]

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