Entity Framework Core : performance tuning when lookup user info from a different dbcontext object

Question

What I want to achieve is to look up a user's full name using their login name from a different source.

For the code below, would it be possible to improve the performance when the view model object from contextA (_context) needs to look up value from table/object in contextB (_peopleContext)?

Currently, the foreach loop is taking the most time, and I would expect it grows as liner when the data grows in request table from contextA.

Or it would be much easier the look up value is in the same DB or context, in this case, that would be contextA?

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

    var result = allRequests.Select(ar => new CreateRequestViewModel
    {
        RequesterName = ar.Login,
        // ... other fields
    });

    // The foreach loop would takes most of the time
    foreach (requestViewModel rvm in result)
    {                        
        rvm.RequesterName = GetFullNameFromPeopleSync(rvm.Login);
    }

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

public string GetFullNameFromDB(string login)
{    
    var names = (from p in _peopleContext.People
                    where p.LogonID.ToLower() == login.ToLower()
                    select new
                        {
                                p.FirstName,
                                p.LastName
                        }).FirstOrDefault();

    return names.FirstName + " " + names.LastName;
}

Answer 1

If it is possible, move everything into one DbContext. If not, you can speedup your query by retrieving all Full Names in one request. Note that pagination should be performed before this request.

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

    var result = allRequests.Select(ar => new CreateRequestViewModel
    {
        RequesterName = ar.Login,
        // ... other fields
    });

    // retrieve TotalCount before pagination
    var totalCount = await result.CountAsync();

    var result = await allRequests
        .Skip((page ?? 1 - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    var logins = result.Select(r => r.Login).ToList();

    var fullNamesLookup = await GetFullNamesFromDBAsync(logins);

    // Initilize the RequesterName field with the full name from the lookup
    foreach (requestViewModel rvm in result)
    {                  
        if (fullNamesLookup.TryGetValue(rvm.Login, out var fullName))      
            rvm.RequesterName = fullName;
    }

    // Return the PaginatedList using ViewModel List created above with paging to the page.
    var paginatedList = new PaginatedList<T>(result, totalCount, page ?? 1, pageSize);

    return View(paginatedList);
}

public async Task<Dictionary<string, string>> GetFullNamesFromDBAsync(IEnumerable<string> logins)
{    
    var lowerCaseLogins = logins.Select(l => l.ToLower()).ToList();
    var names = await _peopleContext.People
        .Where(p => lowerCaseLogins.Contains(p.LogonID.ToLower()))
        .Select(p => new
        {
            p.LogonID,
            p.FirstName,
            p.LastName
        }).ToListAsync();

    return names.ToDictionary(n => n.LogonID, n => n.FirstName + " " + n.LastName);
}

Also note, if your database/column LogonID has Case Insensitive Collation, ToLower() is not needed and may improve performance if you have index on that column.