EFCore 3.1 + CosmosDb can't translate any() on owned object

[darthg8r]

I have a query that seems to be pretty basic. I'm using Core3.1 on top of CosmosDb. I simply want to query a list of orders that have order details, but I'm getting this error:

.Where(b => EF.Property>(b, "OrderDetails") .Any())' could not be translated. Either rewrite the query in a form that can be translated or switch to client evaluation.

Client evaluation isn't an option

Consider this contrived example:

public class Order
{
    public List<OrderDetail> OrderDetails {get;set;}
}

public class OrderDetail
{

}

// Db Context
modelBuilder.Entity<Order>().OwnsMany(x => x.OrderDetails);

// The query
context.Orders.Where( order => order.OrderDetails.Any());

I do this in my sleep with SQL. What gives?

[smitpatel]

Can you provide us what would be expected SQL server side to get the same results?

[darthg8r]

If this were on SQL server instead of Cosmos, you could use this to get the orders that have OrderDetails

select * from Orders where id in ( select distinct OrderId from OrderDetails )

Or

Select * from Orders inner join OrderDetails on Order.Id = OrderDetails.OrderId

[smitpatel]

If this were on SQL server instead of Cosmos

We are talking about Cosmos translation. Cosmos does not run all the SQL, SqlServer can run.

[darthg8r]

It's surely possible to get a list of objects that has more than one owned object. Right? Seems pretty basic. Mongo can do it.

[smitpatel]

Surely you can use mongo then.

[darthg8r]

Is that your passive aggressive way of saying no, it doesn't support that?

[smitpatel]

It is pretty clear fact that EF Core 3.1 does not support that hence you got an exception. If we want to make it working in future version of EF Core then we need a way to represent that in SQL. I don't think discussing about other databases and their capabilities answer that question.

[darthg8r]

It's also pretty clear what I'm trying to accomplish. Instead of passive aggressive, you could have offered a workaround, or a simple, I'm sorry, we don't support querying objects that are owned. The mongodb comment was quite valid considering that you can talk mongo protocol to CosmosDb. I would be using it, but that would mean losing EFCore.

[smitpatel]

There is no work-around else I would have offered you one. While CosmosDb internally use same engine for Azure SQL and mongodb, the protocol to talk to each API is different. Just because CosmosDb mongodb API can do something does not necessarily mean there is SQL equivalent.

[darthg8r]

Ok, here's working SQL select * from c where ARRAY_LENGTH(c.OrderDetails) > 0

[benabdalla-oussama]

Hello everyone,

I've got the same issue. These are my classes.

public class ArticleEntity
{
   public Guid Id { get; set; }
   [Required, StringLength(1000)]
   public string Title { get; set; }

   public ICollection<ArticleTagEntity> Tags { get; set; }
}

public class ArticleTagEntity
{
   public Guid TagId { get; set; }
}

and this is my DB context,

public DbSet<ArticleEntity> Articles { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ArticleEntity>().ToContainer("Articles").Property(p => p.Id).ValueGeneratedOnAdd();
    modelBuilder.Entity<ArticleEntity>().OwnsMany<ArticleTagEntity>(p=> p.Tags);
}

we are using the above classes and we use Odata URL for retrieving, see example below:

https://localhost:44326/odata/articles?$filter=Tags/any(c: c/TagId eq 9c60ad92-bd94-4e78-9ece-3a583e7a1004)

this creates the error below:

InvalidOperationException: The LINQ expression 'DbSet<ArticleEntity>()
.Where(a => EF.Property<ICollection<ArticleTagEntity>>(a, "Tags")
.AsQueryable()
.Any(o => o.TagId == 9c60ad92-bd94-4e78-9ece-3a583e7a1004))' 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.

Also when we do this in LINQ code like below it gives the same error:

Context.Articles.Where(x => x.Tags.Any(i => i.TagId == Guid.Parse("9c60ad92-bd94-4e78-9ece-3a583e7a1004"))).ToList();

This is a test situation, I think this should be supported.

Thank you in advance, Oussama.

[jkears]

@ajcvickers I too am having a similar problem with Entity Framework using CosmosDB provider..

{
    "BCs": [
        {
            "Col2S": [],
            "Test": "string",
            "Id": "3fa85f64-5717-4562-b3fc-2c963f66afa6"
        }
    ],
    "Comment": "string",
    "createdAt": "2021-11-30T17:15:07.6602252+00:00",
    "modifiedAt": null,
    "deletedAt": null,
    "deleted": false,
    "modelVersion": null,
    "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
    "_rid": "JlhzAMTppjMBAAAAAAAAAA==",
    "_self": "dbs/JlhzAA==/colls/JlhzAMTppjM=/docs/JlhzAMTppjMBAAAAAAAAAA==/",
    "_etag": "\"00000000-0000-0000-e622-6bcea78f01d7\"",
    "_attachments": "attachments/",
    "_ts": 1638301354
}

When I execute this simple query...

var v = context.Test1s
                .Where(t => t.BCs.All(o => o.Test == "string")).ToList();

I get the same unsupported exception based upon this generated output...

'DbSet<Test1>()
    .Where(t => EF.Property<List<BC>>(t, "BCs")
        .AsQueryable()
        .All(o => o.Test == "string"))'    

This seems like the most basic of requirements for the EF Cosmos provider.

Is there a work-around?

Thanks in advance.

[ajcvickers]

@jkears If you can express it in SQL that Cosmos understands, then you can try using FromSqlRaw.

[ggirard07]

I am seeing similar issue but not even on owned entity in my case, directly at root entity level (DbSet). Tested with AnyAsync. Can I assume it is all related to same issue (and should be supported at same time) or am I better opening a distinct issue?