Join" not supported in 3.3.0, other options?

Programming
, , ,
1

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

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.

2

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.