Using .Net 6, Azure.Cosmos 3.33
============= Some extra context, only to be thorough ==============
the question is really about the several ways of querying items in CosmosDb 3, but to avoid misunderstandings here is a full disclaimer of the underlying infrastructure :
public interface IWithKey<out TK>
{
public TK Id { get; }
}
public interface IWithPartitionKey<out TK>
{
public TK PartitionKey { get; }
}
public interface ICosmosDbEntity<out TK, PK> : IWithKey<TK>, IWithPartitionKey<PK> where TK : struct
{
}
public abstract class CosmosDbEntity<TK, PK> : ICosmosDbEntity<TK, PK> where TK : struct
{
[JsonPropertyName("id")] public TK Id { get; protected set; }
[JsonIgnore] public virtual PK PartitionKey { get; } = default!;
protected CosmosDbEntity(TK id)
{
Id = id;
}
}
My actual data class :
public class MyType : CosmosDbEntity<Guid, PartitionKey>
{
[JsonIgnore]
//[Newtonsoft.Json.JsonIgnore]
public override PartitionKey PartitionKey => SomeGuid.AsPartitionKey();
public Guid SomeGuid { get; }
public MyType(Guid id, Guid someGuid) : base(id)
{
SomeGuid = someGuid;
}
}
The custom serializer class, designed to use system.Text.Json instead of Newtonsoft's Json.Net :
public class CosmosNetSerializer : CosmosSerializer
{
private readonly JsonSerializerOptions? _serializerOptions;
public CosmosNetSerializer() => _serializerOptions = null;
public CosmosNetSerializer(JsonSerializerOptions serializerOptions) =>
this._serializerOptions = serializerOptions;
public override T FromStream<T>(Stream stream)
{
using (stream)
{
if (typeof(Stream).IsAssignableFrom(typeof(T)))
{
return (T)(object)stream;
}
return JsonSerializer.DeserializeAsync<T>(stream, _serializerOptions).GetAwaiter().GetResult();
}
}
public override Stream ToStream<T>(T input)
{
var outputStream = new MemoryStream();
JsonSerializer.SerializeAsync<T>(outputStream, input, _serializerOptions).GetAwaiter().GetResult();
outputStream.Position = 0;
return outputStream;
}
}
And how the Cosmos client gets instantiated :
var options = new CosmosClientOptions
{
ConnectionMode = //...,
// JsonSerializerDefaults.Web normally makes fields comparison camel-case
Serializer = new CosmosNetSerializer(new(JsonSerializerDefaults.Web))
};
// Cosmos version 3.33
return Microsoft.Azure.Cosmos.CosmosClient
.CreateAndInitializeAsync(connectionStrings.CosmosDb,
credentials, listOfContainers, options)
.GetAwaiter()
.GetResult();
============= end of context ==============
Now, consider those several ways of querying items in my Azure Cosmos db :
Guid id = ...;
string partitionKey = ...;
1. ReadItemAsync (with partition key) => OK
var response = container.ReadItemAsync<MyType>(id.ToString(),
new PartitionKey(partitionKey)).Result;
var item = response?.Resource;
Assert.NotNull(item);
2. GetItemLinqQueryable (without partition key) => NOT OK
var item = container.GetItemLinqQueryable<MyType>(true)
.Where(m => m.Id == id)
.AsEnumerable()
.FirstOrDefault();
Assert.NotNull(item);
3. GetItemLinqQueryable (without 'Where') + DeleteItemAsync (with partition key) => OK
var items = container.GetItemLinqQueryable<MyType>(true)
.ToList();
foreach (var item in items)
{
container.DeleteItemAsync<MyType>(item.Id.ToString(), new PartitionKey(partitionKey)).Wait();
}
4. With iterator (without partition key) => OK
var items = container.GetItemLinqQueryable<MyType>(true)
.Where(m => m.Id == input.Id) // <-- the clause is still here!
.ToFeedIterator();
while (items.HasMoreResults)
{
var item = items.ReadNextAsync().Result;
Assert.NotNull(item);
}
5. : GetItemLinqQueryable (with partition key) => NOT OK
var options = new QueryRequestOptions
{
PartitionKey = new PartitionKey(partitionKey)
};
var item = container.GetItemLinqQueryable<MyType>(
true,
null,
options // <-- there IS a partition key!
)
.Where(m => m.Id == input.Id);
.FirstOrDefault();
Assert.NotNull(item);
6. GetItemQueryIterator (without partition key) => OK
var query = container.GetItemQueryIterator<MyType>(
$"select * from t where t.id='{itemId.ToString()}'");
while (query.HasMoreResults)
{
var items = await query.ReadNextAsync();
var item = items.FirstOrDefault();
}
Problem :
#1, #3, #4, #6 work, but #2 and #5 fail. In #2 and #5, item is null. Why can method #2 or #5 not find the item?
Troubleshooting
At first I thought it might be cause by my custom CosmosSerializer (maybe the id was not compared properly -- despite the fact that my serializer does not touch it, it only works with another special field) but #3 seems to prove but that's not it, as it works with te id too.
Obviously I always checked that the item was present before querying it. I set a breakpoint and go see the CosmosDb container, and even check that the Guid is correct.
I tried with PartitionKey.None in Scenario #5 ... didn't help
I tried adding [JsonPropertyName("id")] above the declaration of Id, to be sure that it wasn't a casing issue. But Scenario #4 disproved that casing is the issue anyways! (the .Where(...) adds a WHERE Id=... with a capital 'i' in the query and it still works)
The solution/answer has been given by the devs of the Cosmos SDK, directly on their forums.
Here is what they wrote :
Currently, SDK doesn't support custom serializers in GetItemLinqQueryable .
If you invoke container.GetItemLinqQueryable<MyType>(true).Where(m => m.Id == id).Expression then you can see translated to SQL query.
It translates to : SELECT VALUE root FROM root WHERE (root["Id"] = <some id>).
As you can see, it uses original the property name (Id with a capital 'i'), not the custom name from JsonPropertyName attribute (id in lowercase). It's a known issue and the SDK team working on this.
See related LINQ queries doesn't use custom CosmosSerializer #2685 for more information.
This part of code:
.Where(m => m.Id == input.Id).FirstOrDefault(); raises Microsoft.Azure.Cosmos.Linq.DocumentQueryException : 'Method 'FirstOrDefault' is not supported.
Currently, SDK does not directly support FirstOrDefault() method on the GetItemLinqQueryable query.
See LINQ to SQL translation - Azure Cosmos DB for NoSQL | Microsoft Learn for more information.