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