EntityFrameworkCore Performance tuning when filter one dbcontext object using the other dbcontext object

Question

I asked something similar recently and it got solved by using AsQueryable: How to save time when pulling data from DB using Entity Framework Core in controller with paging?

On top of that, I am wondering if it is possible to filter one dbcontext object data using different dbcontext data using code below:

public IActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
    var allRequests = _context.Request
                        .Where(r => r.Status != "Closed").ToList();

    // apply filters to query
    var myRequests = allRequests
                        .Where(ar => GetEmailsByRequestId(ar.RequestId).Contains("[email protected]") == true)
                        .ToList();
    

    var result = myRequests.Select(mr => new CreateRequestViewModel
    {
        RequestId = mr.RequestId,
        // ... other fields
    });

    
    // Return the PaginatedList using ViewModel List created above with paging to the page.
    return View(PaginatedList<CreateRequestViewModel>.CreateAsync(result, page ?? 1, pageSize));
}


public string GetEmailsByRequestId(string requestId)
{
    string result = string.empty;

    var emails = (from e in _marketingDBContext.Email
                   where e.ClientRequestId == requestId
                   select new
                   {
                       Email = e.EmailAddress                                              
                   }
                ).ToList();

    //  Logic to convert emails List to string
    ...


    return result;
}

As you can see the Index action has the first _context entity, but it needs to be filtered using second entity marketingDBContext in the GetEmailsByRequestId method.

My current code would have to get all the data from _context first, then filter to return requests having email address equals to [email protected] only from marketingDBContext. I assume it would take a long time if the data grows. As it will scan each record in allRequests to apply the filter.

Not sure if there is any way I can combine the filtering logic?

Thanks in advance.

Answer 1

You have to filer by Ids returned by marketingDBContext.

public IActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
    var allRequests = _context.Request
        .Where(r => r.Status != "Closed");

    var emails = 
        from e in _marketingDBContext.Email
        where e.EmailAddress == "[email protected]"
        select e.ClientRequestId;

    var ids = emails.ToList();
        
      // apply filters to query
    var myRequests = allRequests
        .Where(ar => ids.Contains(ar.RequestId));
    
    var result = myRequests.Select(mr => new CreateRequestViewModel
    {
        RequestId = mr.RequestId,
        // ... other fields
    });

    
    // Return the PaginatedList using ViewModel List created above with paging to the page.
    return View(PaginatedList<CreateRequestViewModel>.CreateAsync(result, page ?? 1, pageSize));
}