CosmosDb vs System.Text.Json

Damn.  Another Update.

I wrote a post, then got help from some nice people.  Then published the "Update" below.  

Then, in the process of using a suggestion from Matias I found a real no-compromise solution.

Stop reading this and go here to get these two working together properly.

Update!

When I wrote this post, I had been doing the two-steps-forward-one-step-back dance for two days and was just generally tired of struggling. I think my tone could have been better.
But as my father used to say, "It's not what you know, it's who you know."
Sure enough, I posted this in the Discord server for DevBetter and the nice folks there put it into the wind.
Less than 24 hours later, I've heard from multiple people at Microsoft, including the Matias, the author of some code I linked below as well as other people related to the products involved.  Wow.
This.  This is why I put my faith in this group of engineers. No one gets it right every time.  What matters is what happens next.
Matias contacted me and pointed me to newer, better code.  I've installed it and while it didn't fix the problem entirely, it looks and seems better/simpler so that's an improvement.
For the record, I've reduced some of the whining in this post.  But not all of it because I'm a snarky old man.

Here's the app scenario:

  • The back end is C# Asp.Net Core v7 API and uses both EF Core for SQL Server and the CosmosDb SDK for CosmosDb
  • The client is Blazor WASM - it talks only to the API
  • In particular - the use of a ValueObject as an "id" is central - that is, using AccountId(Guid idValue) instead of just a Guid. I want these to appear on both sides of the application as the same class.

System.Text.Json is included in .Net 7 libraries.  Microsoft is trying to deprecate Newtonsoft.Json for security and performance reasons.

Which is great.  More performant, no additional dependencies and most importantly, the download payload to the client is that much smaller.  One of the drawbacks to WASM projects is the first time download of all the code. The less the merrier.

but no

CosmosDb is using Newtonsoft.Json under the covers.  And the practical implication is that if you are trying to use System.Text.Json serializer attributes in your classes, CosmosDb will ignore them.  You might do that to keep some properties out of the serialized version of the class, using [JsonIgnore] or change a property name using [JsonPropertyName("MyPropertySpecialName")]. Not great.

But, the CosmosDb docs say, you can add your own custom serializer.  That seems a bit extreme, I just wanted to use their database with their recommended System.Text.Json serializer, not write low level stuff. But OK.

Luckily Matias Quaranta has done the work, and you can find it here.  

But don't use the link above.  Use this newer one, instead: better JsonObjectSerializer

I modified my version slightly to add a constructor, but otherwise it's the same code.  Thanks, Matias.

If you use his code unmodified, your CosmosDb client options will look like this:

var cosmosClientOptions = new CosmosClientOptions()
{
    ConnectionMode = ConnectionMode.Direct,
    HttpClientFactory = httpClientFactory.CreateClient,
    Serializer = new TextJsonSerializer(),
};
Sample Cosmos Client options with unmodified custom serializer, TextJsonSerializer.

Voila, your serializer attributes are now working as planned.  CosmosDb is using the custom serializer.

but no

When creating the CosmosDb client, you have to decide if you want CamelCase property names or not.  Typically, that's what most people use and importantly, the property name of id is reserved by, and required for, CosmosDb to work.

That is, like it not, you're going to have a property with a name of "Id" and it's going to be lower case: id.

The simple solution is to use CamelCase naming for all your properties, the same as you would for serializing them back to the client.  That way everything matches all the way through.  Great now everything matches.

but no

As it turns out, when you use the LINQ capabilities of the CosmosDb SDK, it doesn't respect your System.Text.Json based serializer.  So why is that a problem?

Because it turns this LINQ query:

var query = container
    .GetItemLinqQueryable<Invitation>()
    .Where(i => i.InvitationId.IdValue == invitationId.IdValue);
A typical LINQ query using value object id's.

into this

SELECT VALUE root FROM root WHERE 
(root["InvitationId"]["IdValue"] = "d8ca25ab-dd2f-434c-9e92-912f32aa923b")
What happens when CosmosDb doesn't respect CamelCase settings on queries.

And if you have used CamelCase property name settings in the database, that query will yield no results.  Because what you really wanted was this:

SELECT VALUE root FROM root WHERE 
(root["invitationId"]["idValue"] = "d8ca25ab-dd2f-434c-9e92-912f32aa923b")
A working query with CamelCase parameters.

And no surprise by now, the JsonSerializer setting of PropertyNameCaseInsensitive on queries doesn't seem to work.  I really don't know anything about the insides of the CosmosDb SDK, but I'll bet it's still using Newtonsoft.Json down in the bowels of the SQL translation somewhere.

Here are the workarounds I used to finally get it working:

CosmosDb client

  1. Use the custom serializer in the options for CosmosDb.  This will make CosmosDb respect your JsonAttributes on your documents (but not the LINQ queries).
  2. Do not use CamelCase for property names. This will allow your LINQ queries to have the proper case to match your property names, since CosmosDb isn't respecting your custom serializer.

Your CosmosDb options should look like this:

// note that the PropertyNamingPolicy has been commented out!

JsonSerializerOptions options = new()
{
    DefaultIgnoreCondition = JsonIgnoreCondition.Never,
    WriteIndented = true,
    PropertyNameCaseInsensitive = true,
    //PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
};
var cosmosClientOptions = new CosmosClientOptions()
{
    ConnectionMode = ConnectionMode.Direct,
    HttpClientFactory = httpClientFactory.CreateClient,
    Serializer = new CosmosSystemTextJsonSerializer(options),
};
Cosmos client options without CamelCase specified.

Property attributes

Use [JsonPropertyName] on the id property to force it into CamelCase for the purposes of CosmosDb. In my sample below, you can see I'm using a value object and passing its value to the id required by CosmosDb.

  [JsonPropertyName("id")]
  public override string Id { get => InvitationId.ToString(); }

Oh, and don't forget the other fun fact that System.Text.Json attributes don't flow down from interfaces or abstract properties to the actual concrete class.  The attribute must be attached to the implementation of the property.  See this.

Remember if you are doing a "point read" using an id and partitionKey, you won't be doing a query, but you won't be using your value object id, either.

Public Service Announcement - don't use a property name of "value" inside any of your documents for CosmosDb.  Even though it is clearly a property name, the SQL that's generated seems to think it's a keyword for grouping.  No, you won't get any errors.  Or results.

If I'm wrong about any of this, I'd love to hear about it.  Contact me on Twitter

What to do

That's tricky.  

On one hand, dropping back to Newtonsoft.Json is probably flat out the easiest.  I think I read somewhere that between CosmosDb SDK v4 and .Net 8 many of these issues will be handled.  All of these problems just go away.  Maybe.  But that could be a minute.  They're busy and doing great stuff with areas like performance, etc.

But the gotcha is that using the proper serializer is a big deal.  There are converters and inheritance hierarchies to deal with and they are handled very differently depending on the package you select.  

As doable as it sounds to just swap them out, it won't be trivial, especially if you've gone to production.

Me? I'm going to stick it out.  I have too much invested in other System.Text.Json areas to punt.

p.s. Some of you might ask why I didn't use EF Core for CosmosDb.  I wanted to.  I tried.  Really hard actually.  Had the whole thing working.

but no

As it turns out (and I can't find the link now) you can't filter parent documents using a filter on the members of a child collection in a document using EF Core.

Say you want to select only the Customers who have Invoices with a total Amount of greater than $50.  It works for SQL Server, but for CosmosDb it wants to bring back all the Customers and all their invoices, then filter them.  I couldn't believe this, but if you try it, it'll tell you to do it on the client.

please no, not on the client.