I have a list that is the result of a db query. I need to be able to query another table but filter that query based on the first list. In a perfect world I would join these two tables but they are from different databases that reside on different servers and as far as I know the only way to do this is to link the servers which I'm not allowed to do.

I have already queried both tables and joined the results in a separate list, but the second query returns massive results when run wide-open and it bogs everything down. list1 is a list of Areas that references "Operations" which correlates to "OperationName" in list2.

I thought I could add a ".where" clause to list2 like this but it didn't work:

.Where(a => list1.Any(b => b.Subareas.Any(c => c.Operations.Any(d => d.Name == a.OperationName))))

Note: selectedArea!.Name is a form value the user must select before any of this happens.

private async Task GetRecords(
{
    var list1 = await dbcontext1.Areas
        .Where(a => a.Name == selectedArea!.Name) 
        .Include(a => a.Subareas)
            .ThenInclude(a => a.Operations)
        .ToListAsync();
        
    var list2 = await dbcontext2.ProdDetails
        .Where(a => !string.IsNullOrEmpty(a.OperationName))         
        .Select(a => new
        {
            Customer = a.Customer,
            Assembly = a.Assembly,
            AssemblyDescription = a.AssemblyDescription,
            AssemblyRevision = a.AssemblyRevision,
            WorkOrder = a.WorkOrder,
            Batch = a.Batch,
            BatchQuantity = a.BatchQuantity,
            Barcode = a.Barcode,
            LastWipTime = a.LastWipTime,
            InProcess = a.InProcess,
            OperationName = a.OperationName
        })
        .ToListAsync();
        
        //do more stuff not relevant to this question...
}

Hopefully I have explained myself clearly enough.