Cosmos Db linq query for child item not working

0
votes

I spent so many hours today on this without success, I hope someone can help me. I'm trying Cosmos DB and LINQ, here the items in the db:

|Customer
    |String Property1
    |String Property2
    |ICollection Orders
        |String PropertyA   <--Select on this property

How can I select the Item Customer which has the PropertyA with a specific value?

I tried this and so many other:

var customer = await context.Customers.Select(_s => _s.Orders.Select(p => p.PropertyA == "123456")).FirstAsync()

Thank you for your help.

EDIT 1: I also tried this:

        var customer1 = (from _customer in context.Customers
                         where _customer.Orders.Any(_a => _a.MyId.Contains("2012031007470165"))
                        select _customer).ToList();

Here the error message i received:

The LINQ expression 'DbSet() .Where(c => EF.Property<ICollection>(c, "Orders") .AsQueryable() .Any(o => o.MyId.Contains("2012031007470165")))' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

2
linqazure-cosmosdb

2 Answers

0
votes

If I understand your question properly

var customer = await ctx.Customers.Include(c => c.Orders).FirstAsync(s => s.Orders.FirstAsync(p => p.PropertyA == "123456"));
0
votes

I found the solution, the right answer (use with cosmos SQL api and no more with Entity Framework) is:

            Customer _customerResult = _DBcontainer.GetItemLinqQueryable<Customer>(true)
                                        .Where(_w => _w.Orders.Any(c => c.Purchase_Id == "1234"))
                                        .AsEnumerable()
                                        .FirstOrDefault();