Cosmos: equality on a hierarchical partition-key prefix is lifted out of WHERE into the partition hint, forgoing the index and scanning the physical partition

[vitorlinares]

Bug description

EF Core version: 9.0.17 (also reproduced on 8.0.x)
Provider: Microsoft.EntityFrameworkCore.Cosmos

Summary

For a container with a hierarchical (multi-level) partition key {CaseId, PartyId}, a LINQ query filtering by the prefix property CaseId generates SQL where the CaseId equality has been removed from the WHERE clause and expressed only as a partition-key hint. Because the key is no longer in the WHERE, the /CaseId index is not used, and Cosmos scans the physical partition. The equivalent SDK query that keeps WHERE c.CaseId = @id performs an index seek and is ~80× cheaper for the identical result.

Query and generated SQL

await ctx.CaseParties.Where(x => x.CaseId == caseId).ToListAsync();

EF emits (from CosmosEventId.ExecutedReadNext):

SELECT VALUE c FROM root c
Partition='["<caseId>"]'          <-- CaseId became the partition hint

Note the CaseId predicate is absent from the query body — it is only in Partition=.

RequestCharge measured (container with 51,053 documents; the filtered CaseId matches 148)

Query | RequestCharge -- | -- EF .Where(x => x.CaseId == id).ToListAsync() | 2,725 RU EF .Where(id).Select(x => new { x.id, x.PartyId }) | ~2,720 RU EF .IgnoreQueryFilters().WithPartitionKey(id).Where(x => !x.Deleted) | 2,725 RU SDK SELECT * FROM c WHERE c.CaseId = @id (predicate kept) | 32.7 RU

All return the same 148 documents. WithPartitionKey does not improve on the LINQ form.

Expected behavior

When filtering by a hierarchical partition-key prefix, EF should either (a) keep the prefix predicate in the WHERE clause so the index is used, or (b) provide a supported way to force it. Today there is no LINQ formulation that produces the cheap index seek — the predicate is always lifted out.

Repro

Attached, self-contained, runs against the Cosmos emulator. It creates a container with {/CaseId, /PartyId}, seeds 200 cases × 100 parties (20,000 docs in one physical partition), then prints the generated SQL + RequestCharge for the EF query and the equivalent SDK query so the gap is visible.

Why it matters

On a production workload this turned per-item child-collection reads during a bulk sync into full physical-partition scans, saturating the container's RU budget and causing sustained HTTP 429s. Our workaround is to bypass the LINQ provider and issue the query via the Cosmos SDK with the key kept in the WHERE.

Your code

// Self-contained repro for EF Core Cosmos: an equality filter on a HIERARCHICAL partition-key
// PREFIX property is lifted into the partition routing hint and REMOVED from the WHERE clause,
// which forgoes the property's index and scans the physical partition. The equivalent Cosmos SDK
// query that keeps the predicate in the WHERE uses the index and is dramatically cheaper.
//
// Runs against the Cosmos DB emulator or a test account (reads the connection string from the
// COSMOS_CONNECTION_STRING environment variable). Seeds one container, then compares the
// RequestCharge of the EF query vs the raw SDK query for the identical result set.
//
//   set COSMOS_CONNECTION_STRING=AccountEndpoint=...;AccountKey=...
//   dotnet run
//
// Package: Microsoft.EntityFrameworkCore.Cosmos 9.0.x  (also repro'd on 8.0.x)

using Microsoft.Azure.Cosmos;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

// Set COSMOS_CONNECTION_STRING to your Cosmos DB emulator (or test account) connection string, e.g.
//   AccountEndpoint=https://localhost:8081/;AccountKey=<emulator-key>
var connectionString = Environment.GetEnvironmentVariable("COSMOS_CONNECTION_STRING")
    ?? throw new InvalidOperationException("Set the COSMOS_CONNECTION_STRING environment variable.");
const string DatabaseId = "efcore-hpk-repro";
const int CaseCount = 200;         // distinct partition-key prefixes
const int PartiesPerCase = 100;    // rows per prefix  -> 20,000 rows total in one physical partition

// ---- seed via the SDK (independent of EF) --------------------------------------------------------
var client = new CosmosClient(connectionString);
var db = (await client.CreateDatabaseIfNotExistsAsync(DatabaseId)).Database;

// Hierarchical (composite) partition key: /CaseId then /PartyId
var containerProperties = new ContainerProperties(
    id: "CaseParties",
    partitionKeyPaths: new List<string> { "/CaseId", "/PartyId" });
await db.CreateContainerIfNotExistsAsync(containerProperties, throughput: 10000);

var container = db.GetContainer("CaseParties");

// Seed only once.
var probe = "case-0";
var seeded = container
    .GetItemLinqQueryable<CaseParty>(allowSynchronousQueryExecution: true)
    .Where(x => x.CaseId == probe)
    .Count();
if (seeded < PartiesPerCase)
{
    Console.WriteLine($"Seeding {CaseCount * PartiesPerCase} documents...");
    for (var c = 0; c < CaseCount; c++)
    {
        for (var p = 0; p < PartiesPerCase; p++)
        {
            var doc = new CaseParty
            {
                id = $"case-{c}:party-{p}",
                CaseId = $"case-{c}",
                PartyId = $"party-{p}",
                Deleted = false,
                Blob = new string('x', 400)   // give each doc some body so a scan is measurable
            };
            var fullKey = new PartitionKeyBuilder().Add(doc.CaseId).Add(doc.PartyId).Build();
            await container.CreateItemAsync(doc, fullKey);
        }
    }
    Console.WriteLine("Seed complete.");
}

// ---- 1) EF Core query ----------------------------------------------------------------------------
var opts = new DbContextOptionsBuilder<ReproContext>()
    .UseCosmos(connectionString, DatabaseId)
    .LogTo(Console.WriteLine, new[] { CosmosEventId.ExecutedReadNext }) // prints generated SQL + RequestCharge
    .Options;

Console.WriteLine("\n================ EF Core: .Where(x => x.CaseId == probe) ================");
using (var ctx = new ReproContext(opts))
{
    var efResult = await ctx.CaseParties.Where(x => x.CaseId == probe).ToListAsync();
    Console.WriteLine($"EF returned {efResult.Count} rows (see 'Executed ReadNext' log above for Partition=... and RequestCharge).");
}

// ---- 2) Equivalent raw SDK query -----------------------------------------------------------------
Console.WriteLine("\n================ Cosmos SDK: WHERE c.CaseId = @cid ================");
double sdkRu = 0;
var q = new QueryDefinition("SELECT * FROM c WHERE c.CaseId = @cid AND (NOT IS_DEFINED(c.Deleted) OR c.Deleted = false)")
    .WithParameter("@cid", probe);
using (var it = container.GetItemQueryIterator<CaseParty>(q))
{
    var n = 0;
    while (it.HasMoreResults)
    {
        var page = await it.ReadNextAsync();
        sdkRu += page.RequestCharge;
        n += page.Count;
    }
    Console.WriteLine($"SDK returned {n} rows, RequestCharge = {sdkRu:F1} RU");
}

Console.WriteLine("\nCompare the EF RequestCharge (partition scan) with the SDK RequestCharge (index seek).");

public class CaseParty
{
    public string id { get; set; } = "";
    public string CaseId { get; set; } = "";
    public string PartyId { get; set; } = "";
    public bool Deleted { get; set; }
    public string Blob { get; set; } = "";
}

public class ReproContext : DbContext
{
    public ReproContext(DbContextOptions options) : base(options) { }
    public DbSet<CaseParty> CaseParties => Set<CaseParty>();

    protected override void OnModelCreating(ModelBuilder b)
    {
        var e = b.Entity<CaseParty>();
        e.ToContainer("CaseParties");
        e.HasPartitionKey(x => new { x.CaseId, x.PartyId });   // hierarchical partition key
        e.HasNoDiscriminator();
        e.HasQueryFilter(x => x.Deleted == false);
    }
}

Stack traces

Verbose output

EF Core version

9.0.17

Database provider

Microsoft.EntityFrameworkCore.Cosmos

Target framework

.Net 8

Operating system

No response

IDE

No response

[MihuBot]

I'm a bot. Here is a possible related and/or duplicate issue (I may be wrong):

• Cosmos: Extra clauses are removed from the query when extracting hierarchical partition key values #33960

[vitorlinares]

I'm a bot. Here is a possible related and/or duplicate issue (I may be wrong):

• Cosmos: Extra clauses are removed from the query when extracting hierarchical partition key values #33960

Similar but not the same issue, instead of wiping out the non-PK from the where clause, it's now wiping out the PK.