Join" not supported in 3.3.0, other options?

I’m trying to query a Cosmos container for report generation. I have a container.GetItemLinqQueryable<AccountLog>(true) that I’m using to get account_logs. I have two scenarios that have passed:

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) 

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()

I got an exception that Join is not supported in Cosmos. Is there a workaround to compare a Cosmos document to BadResetIDs (an IQueryable that contains activity IDs)? I’ve tried using SelectMany but I’m not sure how to compare two different objects, accountlog and BadResetIDs.

You can try using a subquery in your Where clause to achieve the same result as the Join. Here’s an example:

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

This should give you the count of ActivityIDs that match the criteria in the Where clause and are also present in BadResetIDs.