Cosmos DB: DateTime serialization format causing errors in ordering and filtering

[Henri-Wiechers]

Bug description

DateTimes are serialized to ISO8601 dates but the trailing zeros are dropped in the second fractions. The means that the lexicographic ordering of the values doesn't match the correct ordering. This causes errors in ordering and filtering.

Related Azure/azure-cosmos-dotnet-v3#4904

Your code

using Microsoft.EntityFrameworkCore;

var endpoint = Environment.GetEnvironmentVariable("COSMOS_ENDPOINT")
    ?? throw new InvalidOperationException("COSMOS_ENDPOINT is not set.");

var key = Environment.GetEnvironmentVariable("COSMOS_KEY")
    ?? throw new InvalidOperationException("COSMOS_KEY is not set.");

var databaseName =
    Environment.GetEnvironmentVariable("COSMOS_DATABASE")
    ?? "EfCoreDateTimeRepro";

var options = new DbContextOptionsBuilder<TestDbContext>()
    .UseCosmos(endpoint, key, databaseName)
    .EnableSensitiveDataLogging()
    .LogTo(Console.WriteLine)
    .Options;

await using var db = new TestDbContext(options);

await db.Database.EnsureCreatedAsync();

if (await db.Items.CountAsync() == 0)
{
    db.Items.AddRange(
        new TestItem
        {
            Id = "exact-second",
            Timestamp = new DateTime(
                2026, 1, 1, 0, 0, 0,
                DateTimeKind.Utc)
        },
        new TestItem
        {
            Id = "fractional-second",
            Timestamp = new DateTime(
                2026, 1, 1, 0, 0, 0, 123,
                DateTimeKind.Utc)
        });

    await db.SaveChangesAsync();
}

// The result of the inserts are these docs. Note that the timestamps have a different
// number of decimal digits.

// {
//     "id": "exact-second",
//     "$type": "TestItem",
//     "Timestamp": "2026-01-01T00:00:00Z",
//     "_rid": "fedHAL39aMkBAAAAAAAAAA==",
//     "_self": "dbs/fedHAA==/colls/fedHAL39aMk=/docs/fedHAL39aMkBAAAAAAAAAA==/",
//     "_etag": "\"02009396-0000-3000-0000-6aba61850000\"",
//     "_attachments": "attachments/",
//     "_ts": 1790599557
// }
// {
//     "id": "fractional-second",
//     "$type": "TestItem",
//     "Timestamp": "2026-01-01T00:00:00.123Z",
//     "_rid": "fedHAL39aMkCAAAAAAAAAA==",
//     "_self": "dbs/fedHAA==/colls/fedHAL39aMk=/docs/fedHAL39aMkCAAAAAAAAAA==/",
//     "_etag": "\"02009496-0000-3000-0000-6aba61850000\"",
//     "_attachments": "attachments/",
//     "_ts": 1790599557
// }


// Problem 1: Incorrect ordering.

var all = await db.Items.OrderBy(x => x.Timestamp).ToListAsync();

Console.WriteLine(string.Join(",", all.Select(x => x.Id)));

// `all` is ['fractional-second', `exact-second`]
// The order is wrong because "2026-01-01T00:00:00Z" > "2026-01-01T00:00:00.123Z".


// Problem 2: Incorrect filtering

var boundary = new DateTime(
    2026, 1, 1, 0, 0, 0,
    DateTimeKind.Utc);

var results = await db.Items
    .Where(x => x.Timestamp > boundary)
    .OrderBy(x => x.Timestamp)
    .ToListAsync();

Console.WriteLine(results.Count);

// Results is empty but it should contain "fractional-second".
// It doesn't because boundary is serialized to "2026-01-01T00:00:00Z"
// and "2026-01-01T00:00:00Z" > "2026-01-01T00:00:00.123Z".

// info: 2026/09/28 14:51:26.494 CosmosEventId.ExecutedReadNext[30102] (Microsoft.EntityFrameworkCore.Database.Command)
//       Executed ReadNext (349.2587 ms, 2.79 RU) ActivityId='a336e1d3-fe82-4e90-b053-7166421065f9', Container='Items', Partition='None', Parameters=[@boundary='2026-01-01T00:00:00Z']
//       SELECT VALUE c
//       FROM root c
//       WHERE (c["Timestamp"] > @boundary)
//       ORDER BY c["Timestamp"]


public sealed class TestDbContext(DbContextOptions<TestDbContext> options)
    : DbContext(options)
{
    public DbSet<TestItem> Items => Set<TestItem>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestItem>(entity =>
        {
            entity.ToContainer("Items");
            entity.HasKey(x => x.Id);
            entity.HasPartitionKey(x => x.Id);
        });
    }
}

public sealed class TestItem
{
    public string Id { get; set; } = null!;

    public DateTime Timestamp { get; set; }
}

Stack traces

Verbose output

EF Core version

10.0.12

Database provider

Microsoft.EntityFrameworkCore.Cosmos

Target framework

.NET 10.0

Operating system

Windows 11

IDE

No response

[MihuBot]

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

• Option to not truncate datetime when serializing using System.Text.Json runtime#36937

[mohammedwed]

Hi,

I'd like to work on this.

From the repro, the root cause looks like the DateTime being serialized in a
variable-width ISO 8601 form (trailing zeros dropped), which breaks lexicographic
ordering and comparisons on the server. This affects both the stored values and
the query parameters (e.g. @boundary in the log), so both would need the same
normalized format.

Before starting, I plan to check whether the trimming happens in the EF Core
Cosmos provider's type mapping or in the Cosmos SDK serializer, given the linked
Azure/azure-cosmos-dotnet-v3#4904.

Questions for the team:

1. Is changing the default to a fixed-width format (e.g. yyyy-MM-ddTHH:mm:ss.fffffffZ)
   acceptable, or would that be a breaking change for existing data?
2. If so, would you prefer this as opt-in, or as a change to the default?

Happy to go a different direction if you have one in mind.

[mohammedwed]

UPDATE

Reproduction
The repro from the issue behaves as reported on a local build of main
(c2dcb0f): OrderBy returns fractional-second, exact-second, and the
Timestamp > boundary filter returns 0 rows.

Findings
There seem to be two independent places where a DateTime becomes JSON text,
and both currently produce the shortened form (no trailing zeros):

1. Stored values and SQL constants: CosmosStructuralTypeSerializer and
   CosmosTypeMapping.GenerateSqlLiteral call the property's
   JsonValueReaderWriter.ToJson(...), which for DateTime appears to be
   JsonDateTimeReaderWriter (Utf8JsonWriter.WriteStringValue(DateTime)).
2. Query parameters: SqlParameter.Apply calls queryDefinition.WithParameter(Name, Value),
   so the SDK serializes the raw value through JsonCosmosSerializer
   (registered in SingletonCosmosClientWrapper), which uses default
   System.Text.Json settings. SqlParameter.ToJsonString does the same.

Because these are separate, fixing only one of them leaves stored values and the
parameter in different layouts, and comparisons still give wrong results. In my
local test, patching only the parameter side gave a filter count of 2 instead of 1.

Experiment
As a proof of concept I made both paths write a fixed-width UTC format
(yyyy-MM-dd'T'HH:mm:ss.fffffff'Z'). On a fresh database the repro then returns
exact-second, fractional-second and a filter count of 1. The branch is here:

https://github.com/mohammedwed/efcore/tree/fix/cosmos-datetime-format

This is only a proof of concept. I have not tested other Kinds
(Local/Unspecified), DateTimeOffset, or other precisions beyond the repro.
I have also not checked whether earlier EF Core versions behave the same way.

Questions before I go further

1. JsonDateTimeReaderWriter lives in the shared EFCore project, so changing it
   would affect relational JSON columns too. Would you prefer a Cosmos-specific
   reader/writer or type mapping?

2. Changing the default format means existing documents in the short format
   would compare inconsistently with new ones. Should this be opt-in, a change to
   the default, or handled some other way?

3. For parameters, would you prefer routing them through the type mapping's
   reader/writer so there is a single place that decides the format, instead of
   configuring the serializer?

Happy to take a different direction if you have one in mind.

[AndriySvyryd]

Is changing the default to a fixed-width format (e.g. yyyy-MM-ddTHH:mm:ss.fffffffZ)
acceptable, or would that be a breaking change for existing data?

It's acceptable, as long as it can still read existing data

If so, would you prefer this as opt-in, or as a change to the default?

Default.

JsonDateTimeReaderWriter lives in the shared EFCore project, so changing it
would affect relational JSON columns too. Would you prefer a Cosmos-specific
reader/writer or type mapping?

It's ok to change the shared implementation.

Changing the default format means existing documents in the short format
would compare inconsistently with new ones. Should this be opt-in, a change to
the default, or handled some other way?

They are already compared inconsistently, so I doubt that any existing apps would be affected by this. As a workaround a custom ReaderWriter can be used if necessary.

For parameters, would you prefer routing them through the type mapping's
reader/writer so there is a single place that decides the format, instead of
configuring the serializer?

Yes, this is the preferred approach.