How to get Point-Read with EF Core 6.0 & CosmosDB, Find & FindAsync methods

[shahabganji]

How to get a point read with Find or FindAsync methods.

I saw in the documentations of What's new? For EF Core 6.0 that a point read could be achieved if Id and PartitionKey passed to the Find method; however, this seems to not be working no matter how the entity ID configured, I tried various configurations, but none worked properly. This repository helps to reproduce the issue we have at the Production.

Code sample from Microsoft Documentation:

var isosceles = context.Triangles.Find("Isosceles", "TrianglesPartition");

info: 8/30/2021 14:53:39.326 CosmosEventId.ExecutingReadItem[30101] (Microsoft.EntityFrameworkCore.Database.Command)
      Reading resource 'Isosceles' item from container 'Shapes' in partition 'TrianglesPartition'.
info: 8/30/2021 14:53:39.330 CosmosEventId.ExecutedReadItem[30103] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed ReadItem (1 ms, 1 RU) ActivityId='3c278643-4e7f-4bb2-9953-6055b5f1288f', Container='Shapes', Id='Isosceles', Partition='TrianglesPartition'

---

Followings are two different approaches I tried,

// omitted code for clarity
  builder.HasKey(p => new {p.Id, p.Owner});
        builder.Property(p => p.Id)
            .ToJsonProperty("id")
            .IsRequired();
        
        builder.HasPartitionKey(p => p.Owner);
        builder.Property(p => p.Owner).IsRequired();
// omitted code for clarity

// omitted code for clarity
   builder.HasKey(p => p.Id);
        builder.Property(p => p.Id)
            .ToJsonProperty("id")
            .IsRequired();
        
        builder.HasPartitionKey(p => p.Owner);
        builder.Property(p => p.Owner).IsRequired();
// omitted code for clarity

The latter one, is throwing an exception at runtime if you pass both Id and PartitionKey mentioning that the entity is configured with one key; this approach might be useful when both the ID and partition key are the same property, tho. It did not work for entities with that condition too, the same property being partition key and id.

Unhandled exception. System.ArgumentException: Entity type 'Draft' is defined with a single key property, but 2 values were passed to the 'Find' method.
   at Microsoft.EntityFrameworkCore.Internal.EntityFinder`1.FindTracked(Object[] keyValues, IReadOnlyList`1& keyProperties)
   at Microsoft.EntityFrameworkCore.Internal.EntityFinder`1.FindAsync(Object[] keyValues, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.FindAsync(Object[] keyValues)
   at Program.<Main>$(String[] args) in C:\Users\saeed.ganji\dev\playground\EFCore-Array-String\Sample.EFCore.ArrayPrimitiveTypes\Program.cs:line 37

All the time it results to a SQL Query instead of a point read.

var draft = await context.Drafts.FindAsync(Guid.Parse("4042662c-3b7a-4970-aee5-8bdc6d5e7d1d"), "Sample");

dbug: 3/21/2022 14:20:19.826 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query)
      Compiling query expression:
      'DbSet<Draft>()
          .FirstOrDefault(e => object.Equals(
              objA: EF.Property<object>(e, "Id"),
              objB: (object)__get_Item_0) && EF.Property<string>(e, "Owner") == __p_1)'
dbug: 3/21/2022 14:20:19.917 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query)
      Generated query execution expression:
      'queryContext => ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync<Draft>(
          asyncEnumerable: new QueryingEnumerable<Draft>(
              (CosmosQueryContext)queryContext,
              SqlExpressionFactory,
              QuerySqlGeneratorFactory,
              [Cosmos.Query.Internal.SelectExpression],
              Func<QueryContext, JObject, Draft>,
              Sample.EFCore.ArrayPrimitiveTypes.Storage.BooksContext,
              null,
              False,
              True
          ),
          cancellationToken: queryContext.CancellationToken)'
info: 3/21/2022 14:20:19.994 CosmosEventId.ExecutingSqlQuery[30100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing SQL query for container 'Drafts' in partition 'Sample' [Parameters=[@__get_Item_0='4042662c-3b7a-4970-aee5-8bdc6d5e7d1d']]
      SELECT c
      FROM root c
      WHERE (c["id"] = @__get_Item_0)
      OFFSET 0 LIMIT 1
info: 3/21/2022 14:20:21.259 CosmosEventId.ExecutedReadNext[30102] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed ReadNext (845.2267 ms, 2.79 RU) ActivityId='ec4b24b1-60a2-4ff7-8077-a64854c6f893', Container='Drafts', Partition='Sample', Parameters=[@__get_Item_0='4042662c-3b7a-4970-aee5-8bdc6d5e7d1d']
      SELECT c
      FROM root c
      WHERE (c["id"] = @__get_Item_0)
      OFFSET 0 LIMIT 1

Include provider and version information

EF Core version: 6.0.3
Database provider: Microsoft.EntityFrameworkCore.Cosmos
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Rider 2021.3.3

[ggirard07]

Same issue here... tried to avoid using Guid as Key in favor of string instead in case of conversion issues, no changes.
Also tried to isolates my entity types into distinct container and removed discriminator to get it out of query, still no point read.

Edit
Looks like the issue is related to a sub entity, configured as default as a owned entity based on convention, while my root/parent entity is configured through IEntityTypeConfiguration. As soon as I remove any reference to sub entity in my model from root entity, then it works as expected!