Sub queries in LINQ

Writing these damn queries always makes me feel stupid.  

Here's a sample where we need to filter on the values in an object and other values in a list contained inside the object, but need access to the parent object and associated child object when we're finished.

var location = userAccountSummaries
    .Where(summary =>
        summary.AccountType == AccountType.merchant &&
        summary.MerchantUserLocations.Count > 0)
    .SelectMany(filtered => 
        filtered.MerchantUserLocations
            .Where(x =>
                x.Value?.Permissions != null &&
                x.Value.Permissions.Count > 0)
            .Select(item => 
                new { filtered.AccountId, item.Value }))
    .First();

In the case above, we filter first on the values in the parent object, which will return a collection of those parent objects.  This becomes the input to the SelectMany clause.

Then, using SelectMany we use the filtered values to access the items in the list inside each object in the collection.  Because SelectMany "flattens" the collection, we get an entry for each combination of filtered for each one of the items in the child list that matches the second filter.

For example:

The first Where may yield three matching objects, each of which has four MerchantUserLocation items in its list.  

The next SelectMany creates twelve rows, consisting of 3 objects and 4 MerchantUserLocations each.  The second Where is applied, to this list of 12, and finding perhaps 2 that match.

These two become the collection for the Select clause, but because we are still inside the SelectMany clause, we still have access to the filtered variable and the MerchantUserLocation that matched the criteria.

The easiest way to access both is to create an anonymous item that carries both variables.

Note that in the resulting list, since more than one MerchantUserLocation might match the criteria, we would have two entries for the same accountId with different MerchantUserLocation items.

In this case, we simply selected the first.

Programming note: In this example, the MerchantUserLocations is a dictionary, hence the Value qualifier.