Entity Framework Core and Cosmos DB. Using LINQ expressions to read entities

Question

In our solution we´re using the package Microsoft.EntityFrameworkCore.Cosmos 3.1.1 to do operations against our cosmos databases and containers in Azure. We have a fairly easy object structure. An object containg a list of other objects.

public class ExampleEntity : Entity
{
    public string TestProperty { get; set; }
    public IEnumerable<SubEntity> SubEntities { get; set; }

    protected override IEnumerable<object> GetEqualityComponents()
    {
            yield return TestProperty;
    }
}

public class SubEntity : Entity
{
    public bool IsActive { get; set; }

    protected override IEnumerable<object> GetEqualityComponents()
    {
        yield return IsActive;
    }
}

We´ve configured the DbContext for EntityFramework like this:

builder.Entity<ExampleEntity>().ToContainer(nameof(ExampleEntity));
builder.Entity<ExampleEntity>().HasKey(p => p.id);
builder.Entity<ExampleEntity>().OwnsMany(p => p.SubEntities);

The json structure in cosmos looks like this:

{
    "id": "51099fa9-5d71-4181-93b1-2c8cc0482a95",
    "CreatedAt": "2020-02-14T08:11:06.701659Z",
    "Discriminator": "ExampleEntity",
    "TestProperty": "Property1",
    "UpdatedAt": "0001-01-01T00:00:00",
    "SubEntities": [
        {
            "id": "9a120613-c42a-4399-a660-e6228cfce0ad",
            "CreatedAt": "2020-02-14T08:11:06.70457Z",
            "ExampleEntityid": "51099fa9-5d71-4181-93b1-2c8cc0482a95",
            "IsActive": false,
            "UpdatedAt": "0001-01-01T00:00:00"
        },
        {
            "id": "21b86b53-2d6a-4b31-a60b-8d31cfd04734",
            "CreatedAt": "2020-02-14T08:11:06.705145Z",
            "ExampleEntityid": "51099fa9-5d71-4181-93b1-2c8cc0482a95",
            "IsActive": true,
            "UpdatedAt": "0001-01-01T00:00:00"
        }
    ],
    "_rid": "R343APAECLsBAAAAAAAAAA==",
    "_self": "dbs/R343AA==/colls/R343APAECLs=/docs/R343APAECLsBAAAAAAAAAA==/",
    "_etag": "\"06001f30-0000-0d00-0000-5e46561b0000\"",
    "_attachments": "attachments/",
    "_ts": 1581667867
}

Now, we want to search after ExampleEntities where SubEntities has the boolean value of IsActive set to true. This is where our problems starts.

We have a generic repository, where the Read method looks like this:

/// <summary>
/// Get an entity from the database
/// </summary>
/// <param name="predicate">A predicate to decide which entity to get</param>
/// <param name="children">Child entities to included in the DbSet</param>
/// <returns>All entities that matches the predicate</returns>
public async Task<IEnumerable<TEntity>> ReadAsync(Expression<Func<TEntity, bool>> predicate, params Expression<Func<TEntity, object>>[] children)
{
    var dbSet = _dbContext.Set<TEntity>();
    children.ToList().ForEach(p => dbSet.Include(p));

    var entities = dbSet.Where(predicate);
    return await entities.ToListAsync();
}

Using the following code like this in a IntegrationTest:

[Test]
public async Task EntityFramework_Should_Return_Object_Based_On_Property_In_SubEntity()
{
    var uow = Container.Resolve<IUnitOfWork<ExampleEntity>>();
    var entity1 = new ExampleEntity
    {
        TestProperty = "Property1",
        SubEntities = new List<SubEntity>
        {
            new SubEntity
            {
                IsActive = false
            },
            new SubEntity
            {
                IsActive = true
            }
        }
    };

    await uow.Repository.CreateAsync(entity1);
    await uow.CommitAsync();

    var readEntity = uow.Repository.ReadAsync(p => p.SubEntities.Any(p => p.IsActive), p => p.SubEntities);
    readEntity.Should().NotBeNull();
}

The problem occurs at this line here where I use the Read method from the repository above:

var readEntity = uow.Repository.ReadAsync(p => p.SubEntities.Any(p => p.IsActive), p => p.SubEntities);

It results in the following exception:

System.InvalidOperationException: The LINQ expression 'DbSet<ExampleEntity>
    .Where(e => EF.Property<IEnumerable<SubEntity>>(e, "SubEntities")
        .AsQueryable()
        .Any(o => o.IsActive))' 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().

I find it quite strange that a simple query like this isn´t supported in Entity Framework Cosmos. Obviously I can do a AsEnumerable(), but then I will download all the data from the database and do the filtering client side and not on the database side, which will have a huge performance impact when the database contains 100´000s of records..

How can I rewrite my repository to do such filtering on the database side? Is it possible at all with Entity Framework Cosmos?

Answer 1

As per current limitations Include and join are not supported yet.