search

Azure / azure-cosmos-dotnet-v3

.NET SDK for Azure Cosmos DB for the core SQL API
MIT License
743 stars 495 forks source link

ISO datetime strings are not read back correctly from the database #2683

Closed anttikes closed 3 years ago

anttikes commented 3 years ago

This bug was first reported against the dotnet/efcore repository but according to their investigation the problem is in the Cosmos SDK itself.

Describe the bug A string which is formatted to look like a ISO datetime stamp is read back incorrectly from the database. Below is an excerpt from the output of the sample application:

Re-creating database

Writing new item to database '4617064c-382a-409c-8caa-d6e8f88471f2'
        - ISODate: '2021-08-23T06:23:40Z'

Writing new item to database '39d830c6-c486-49b9-8464-4892d81b89d5'
        - ISODate: '2021-08-23T06:23:40+00:00'

Writing new item to database '28f12faa-dedd-4b0d-ae28-f739f4ce2102'
        - ISODate: '2021-08-23T06:23:40+02:00'

Reading back item '4617064c-382a-409c-8caa-d6e8f88471f2'
        - ISODate: '08/23/2021 06:23:40'

Reading back item '39d830c6-c486-49b9-8464-4892d81b89d5'
        - ISODate: '08/23/2021 09:23:40'

Reading back item '28f12faa-dedd-4b0d-ae28-f739f4ce2102'
        - ISODate: '08/23/2021 07:23:40'

To Reproduce The attached ZIP file contains a solution which utilizes EF Core to write and read back a string which is formatted to look like a ISO datetime stamp.

EFCoreISODatesBug.zip

  1. Install the CosmosDB emulator on the local computer
  2. Open up the sample application in Visual Studio
  3. Build it
  4. Run it

Expected behavior Since the model uses a plain string as the property's type the expected behavior is that the strings are saved into CosmosDB as-is, and when they are read back they are still exactly the same as before, with no formatting changes or offset calculations whatsoever.

Actual behavior The items are written correctly to the CosmosDB container, and the content of the documents therein corresponds exactly to the expected outcome.

When the items are read back from CosmosDB then the ISODate property is formatted differently, and the time stamp has automatically been adjusted by the UTC offset. On my computer, when I reproduced the issue, the adjustment was done incorrectly as well. This is evident in the excerpt shown above.

Environment summary Output from 'dotnet --info':

.NET SDK (reflecting any global.json):
 Version:   5.0.400
 Commit:    d61950f9bf

Runtime Environment:
 OS Name:     Windows
 OS Version:  10.0.19043
 OS Platform: Windows
 RID:         win10-x64
 Base Path:   C:\Program Files\dotnet\sdk\5.0.400\

Host (useful for support):
  Version: 5.0.9
  Commit:  208e377a53

.NET SDKs installed:
  3.1.412 [C:\Program Files\dotnet\sdk]
  5.0.104 [C:\Program Files\dotnet\sdk]
  5.0.203 [C:\Program Files\dotnet\sdk]
  5.0.206 [C:\Program Files\dotnet\sdk]
  5.0.303 [C:\Program Files\dotnet\sdk]
  5.0.400 [C:\Program Files\dotnet\sdk]

.NET runtimes installed:
  Microsoft.AspNetCore.All 2.1.29 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.29 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 3.1.18 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 5.0.4 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 5.0.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 5.0.9 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.1.29 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 3.1.18 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 5.0.4 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 5.0.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 5.0.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 3.1.18 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 5.0.4 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 5.0.5 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 5.0.9 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

Underlying Azure SDK version: 3.12.0 Target framework: .NET 5.0 Operating system: Windows 10 Enterprise (version "10.0.19043 Build 19043") IDE: Visual Studio 2019 version 16.11.1 CosmosDB Emulator version: 2.14.1.0 (08dca53e) Operating system time zone settings: "UTC+02:00 Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius"

ealsur commented 3 years ago

SDK uses Newtonsoft.Json by default, Newtonsoft.Json by default will attempt to deserialize date strings and use timezone conversion.

You can either turn off Newtonsoft.Json's automatic parsing by setting the DateParseHandling to None and use string as the model type or have some custom date parsing Newtonsoft.Json converter.

Similar to https://github.com/Azure/azure-cosmos-dotnet-v3/issues/880

ealsur commented 3 years ago

@anttikes Did you try customizing Newtonsoft.Json? Any results?

anttikes commented 3 years ago

@ealsur No, we didn't try customizing it. As stated in https://github.com/dotnet/efcore/issues/25656 our team decided to wait until the flag will be set by default in EF Core.

It should be noted that the model property's type is string. That's the weird part. I would've understood this quirky behavior if the type would've been DateTime or similar.

I'll close this ticket.