Why SelectMany does not work for EF Core Cosmos DB?

Admin 1
Viewed 382

For Entity Framework Core Cosmos provider, I'd like to have a query like

SELECT VALUE child
FROM child IN Families.Children
WHERE Families.FamilyName = @familyName

But executing query below throws an exception:

var result = _context.Families
   .Where(x => x.FamilyName == familyName)
   .SelectMany(f => f.Children)
   .ToListAsync(ct);

"InvalidOperationException: The LINQ expression 'DbSet<Family>
.Where(f => f.FamilyName == __familyName_0)
.SelectMany(
source: f => EF.Property<IList<Child>>(f, 'Children')
.AsQueryable(),
collectionSelector: (f, c) => new TransparentIdentifier<Family, Child>(
Outer = f,
Inner = c
))' 
could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

Why does the expression get translated so it has clearly wrong collecion selector (c,c)?

EDIT: It does not have wrong collection selector. It seems to translate expressions to have a parameter named after first letter of an entity, which may still induce other errors when the entities will be named like CapybaraFamily and CapybaraChild.

But the query still doesn't get translated.

Is something missing in entity configuration?

public void Configure(EntityTypeBuilder<Family> builder)
{
    builder
        .ToContainer("Families")
        .HasNoDiscriminator()
        .HasIndex(x => x.Id)

    builder.OwnsMany(x => x.Children);
        
    builder.Property(x => x.Id).ToJsonProperty("id");
}

Entities:

public class Family : Microsoft.Azure.Documents.Document
{
    public IList<Child> Children { get; set; }
    public string FamilyName { get; set; }
}

public class Child
{
    public string ChildName { get; set; }
    public Guid Id { get; set; }
}

Using Microsoft.EntityFrameworkCore, Version=3.1.8.0

entity-framework-core azure-cosmosdb asp.net-core c#
1 Answers

Unfortunately, you need to do a ToList before the SelectMany. Cosmos has an underlying document structure, so it cannot work with the normal entities to do remote buffering in a sql hash.

Using ToList before the SelectMany will return the entire set locally first, but SelectMany can work locally.

var result = _context.Families
   .Where(x => x.FamilyName == familyName)
   .ToList()
   .SelectMany(f => f.Children);
user_177361810
Related