FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it

I’m trying to compose a raw query that should be composable with Linq operators, but I get this error:

FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.

Steps to reproduce

Run this code:

class Foo
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public DateTime StartDate { get; set; }
    public ICollection<FooTranslation> Translations { get; set; } = new HashSet<FooTranslation>();
}

class FooTranslation
{
    public int FooId { get; set; }
    public string Lang { get; set; }
    public string Name { get; set; }
    public string Content { get; set; }
}

class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
    
    public DbSet<Foo> Foos { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        
        modelBuilder.Entity<Foo>(entity =>
        {
            entity.ToTable("Foos");
            entity.HasKey(f => f.Id);
            entity.OwnsMany(f => f.Translations, e =>
            {
                e.ToTable("FooTranslations");
                e.HasKey(t => new {t.FooId, t.Lang});
                e.Property(t => t.Lang).IsRequired().HasMaxLength(2);
                e.Property(t => t.Name).IsRequired().HasMaxLength(100);
                e.Property(t => t.Content).IsRequired().HasMaxLength(200);
            });
        });
    }
}

...

var sql = @"
WITH foosWithRownum as (
    SELECT Id, CategoryId, StartDate,
        ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY StartDate DESC, Id DESC) rownum
    FROM Foos
    WHERE StartDate < CURRENT_TIMESTAMP
)
SELECT f.*, t.FooId, t.Lang, t.Name, t.Content
FROM foosWithRownum f
LEFT JOIN FooTranslations t ON f.Id = t.FooId
WHERE f.rownum = 1";

IQueryable<Foo> query = dbContext.Foos.FromSqlRaw(sql);

// Add sorts and projections to query with Linq, and execute it
query = query.OrderBy(...)
var result = query.Select(...).ToList();

This gives the error above.

As far as I can tell, my raw SQL should be composable, according to the rules here. There is an ORDER BY, but it’s in an OVER clause in a CTE, so it shouldn’t prevent the whole query from being composable. I tried adding OFFSET 0 as suggested in the docs, but I get a syntax error (it’s probably not supported in an OVER clause). I also tried adding TOP 100 PERCENT to the SELECT clause, but it didn’t change anything.

Is there any way I can run this query with EF Core ?

What is really strange is that I get the same error if I directly call ToList() on the result of FromSqlRaw(), even though I’m not composing with anything.

Further technical details

EF Core version: 3.1.8 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET Core 3.1 Operating system: Windows 10 IDE: irrelevant, I can easily reproduce without any IDE