'Join' Method not supported in cosmos DB 3.3.0,Is there any other option

Question

I am querying for report generation and below are some scenarios scenarios which got pass while

var account_logs =  container.GetItemLinqQueryable<AccountLog>(true)
                             .Where(u => u.AccessedOn > MinDate);
            
var TempGoodResetIDs = (from ll in account_logs
                        where (ll.AccessedOn >= StartDate) &&
                              (ll.AccessedOn <= EndDate) &&  
                              ((ll.Activity == 3) &&
                               ((ll.Result == (int)Log.AccountResult.PasswordReset) ||
                                   (ll.Result == (int)Log.AccountResult.TempPWDSentThroughEmail)))
                        select ll)

This got passed and I gotaccount_log filled with data.

Then I have something like this code:

var BadResetIDs = TempBadResetIDs.Select(ll => ll.ActivityID) 
                                 .Distinct().Except(GoodResetIDs);

var Captcha = (from ll in account_logs
               join b in BadResetIDs on ll.ActivityID equals b
               where ((ll.Activity == 3) && (ll.Result == 5))
               select ll.ActivityID).Count()

Here I got an exception that 'Join' is not supported in Cosmos.Is there a workaround to join cosmos document with BadResetIDs which is an IQueryable and contains activity ID?

I've used SelectMany but not sure how to compare two different objects accountlog and BadResetIDs.

Answer 1

While Cosmos SQL has a JOIN operator it only works by joining data within a single document. Cosmos does not support joining several documents in a query so the LINQ join operator is not supported.

In your case you might be able to solve your problem by performing two queries. However, you will be moving data from the database to the client to create the second query and you run the risk of the database having changed in the meantime.

Having the desire to join documents in a query could be a sign that you are retrofitting a relational database approach on top of Cosmos. Designing your system based on "no SQL" thinking from the start can lead to a very different data model.

If you get really technical there is one exception to a query not being able to combine multiple documents: You can create a stored procedure in javascript that can do that. However, a stored procedure can only execute within a single partition so it's not a general solution to combing multiple documents in a single query.