Присоединение к подзапросу в EF Core ⇐ C#

[Anonymous]

Цель: я хочу выбрать последние версии записей данной таблицы:



идентификатор
версия
имя




1
0
Алек

1
1
Алекс


2
0
Боб



Примечание: временные данные эта функция недопустима.
В идеале я хочу, чтобы EF Core генерировал это с использованием 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 = e.Version;

Примечание. Вот альтернативный, но эквивалентный запрос, если описанное выше невозможно в 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 года на основе ответа https://stackoverflow.com/users/14868997/charlieface
Рабочее решение:
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
{
public void Configure(EntityTypeBuilder 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 Entities { get; set; }

public MyDbContext() : base() {}

public MyDbContext(DbContextOptions 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);
}
}

Стандартный вывод выглядит следующим образом:
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]

Подробнее здесь: https://stackoverflow.com/questions/774 ... in-ef-core