Cosmos DB: Find/FindAsync performs SQL API query when entity has embedded entities

[joelverhagen]

Include your code

When using Cosmos DB + EntityFrameworkCore 5.0.3, the FindAsync and Find methods do not perform the cheaper (w.r.t. RU) document lookup query when there are owned (embedded) entities.

using (var ctx = new TestContext())
{
    await ctx.PackageAggregates.FindAsync(pk, name);
}

...

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var builder = modelBuilder.Entity<PackageAggregate>();
    builder.HasPartitionKey(x => x.PartitionKey);
    builder.Property(x => x.PartitionKey).ToJsonProperty("pk");
    builder.HasKey(x => new { x.PartitionKey, x.LowerName });

    builder.OwnsMany(x => x.Owners); // this line causes the problem
}

When the PackageAggregate entity has no embedded entity, the lookup performs this API call to Cosmos DB. This costs 1 RU.

When there is an embedded entity, a SQL API query is made, costing nearly 3 times as much (3.03 RU).

Full repro code

using Microsoft.Azure.Cosmos;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Net;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        public const string Endpoint = "https://<resource name>.documents.azure.com:443/";
        public const string Key = "<key>";
        public const string Database = "PackageManagement";
        public const string Container = "PackagesEF";

        static async Task Main(string[] args)
        {
            var pk = "my-pk";
            var name = Guid.NewGuid().ToString();

            using (var ctx = new TestContext())
            {
                ctx.PackageAggregates.Add(new PackageAggregate
                {
                    PartitionKey = pk,
                    LowerName = name,
                });
                await ctx.SaveChangesAsync();
            }

            using (var ctx = new TestContext())
            {
                var pa = await ctx.PackageAggregates.FindAsync(pk, name);
                Console.WriteLine("Found? " + (pa != null));
            }
        }
    }

    class TestContext : DbContext
    {
        public DbSet<PackageAggregate> PackageAggregates { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseCosmos(Program.Endpoint, Program.Key, Program.Database, o =>
                {
                    // Use this if you want to use a MITM proxy to verify API calls.
                    // o.WebProxy(new WebProxy("localhost", 20000));
                    // o.ConnectionMode(ConnectionMode.Gateway);
                });
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultContainer(Program.Container);

            var builder = modelBuilder.Entity<PackageAggregate>();
            builder.HasPartitionKey(x => x.PartitionKey);
            builder.Property(x => x.PartitionKey).ToJsonProperty("pk");
            builder.HasKey(x => new { x.PartitionKey, x.LowerName });
            builder.OwnsMany(x => x.Owners);
        }
    }

    public class PackageAggregate
    {
        public string PartitionKey { get; set; }
        public string LowerName { get; set; }
        public List<PackageOwner> Owners { get; set; }
    }

    public class PackageOwner
    {
        public string Username { get; set; }
    }
}

Include provider and version information

EF Core version: 5.0.3
Database provider: Microsoft.EntityFrameworkCore.CosmosDB
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.10 Preview 1 internal

[maximecaron]

We are facing the exact same bug.
is tried ctx.PackageAggregates.WherePartitionKey(partitionKey).Where( x => x.LowerName == value).FirstOrDefaultAsync();
but having the same issue