https://stackoverflow.com/questions/69270982/f-query-find-rows-with-max-value-in-each-group/69274639

NestedQuery.fs

open Microsoft.EntityFrameworkCore
open System.Linq

[<CLIMutable>]
type YourTable =
    {
        id : int
        rev : int
        contents : string
    }

type YourTableDataContext() =
    inherit DbContext()

    [<DefaultValue>]
    val mutable yourTable : DbSet<YourTable>

    member public this.YourTable
        with get() = this.yourTable
        and set(value) = this.yourTable <- value

    override __.OnConfiguring(optionsBuilder : DbContextOptionsBuilder) =
        optionsBuilder
            .UseSqlServer("Server=localhost;Database=YourDatabase;Trusted_Connection=True;")
            |> ignore

    override __.OnModelCreating(modelBuilder: ModelBuilder) =
        modelBuilder.Entity<YourTable>()
            .HasKey([| "id"; "rev" |]) |> ignore

let ctx = new YourTableDataContext()

let nestedQuery =
    query {
        for outer in ctx.YourTable do
        where (
            query {
                for inner in ctx.YourTable do
                groupBy inner.id into grp
                exists (grp.Key = outer.id && grp.Max(fun ent -> ent.rev) = outer.rev)
            })
        select outer
    }

[<EntryPoint>]
let main argv =
    nestedQuery
        |> Seq.iter (printfn "%A")
    0

(*
/****** Object:  Table [dbo].[YourTable]    Script Date: 9/21/2021 3:55:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[YourTable](
	[id] [int] NOT NULL,
	[rev] [int] NOT NULL,
	[contents] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_YourTable] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[rev] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 *)