How can I improve this Entity Framework LINQ query

I have the following EF query that is taking about 20 seconds to return ~100k rows. If I remove the "Addresses" and "Contacts" subqueries (if that's what you call them) in the select statement it takes about 2 seconds to return. Is there a more efficient way to create the Employee object with the lists of addresses and contacts associated with an employee?

            return await (from account in _databaseContext.Accounts
                join client in _databaseContext.Clients on account.AddressClientID equals client.ClientId
                      where client.EffectiveTo == null
                      && client.ClientType.Equals("PERSON")
                      && account.EffectiveTo == null
                      && account.Status.Equals("INFORCE")
                select new Employee
                {
                    Id = account.AccountId,
                    ClientId = client.ClientId,
                    FirstName = client.GivenNames,
                    LastName = client.Surname,
                    Gender = client.Gender,
                    Addresses = client.ClientAddresses.Where(x => x.EffectiveTo == null)
                        .Select(x => AddressFactory.Create(x)).ToList(),
                    Contacts = client.ClientContacts.Where(x => x.EffectiveTo == null && (x.ContactType == "EMAIL" || x.ContactType == "MOBILE"))
                        .OrderBy(y => y.EffectiveFrom)
                        .Select(z => ContactFactory.Create<Employee>(client, z)).ToList(),
                    DateOfBirth = client.DateOfBirth
                }).ToListAsync(cancellationToken);

Solution

This would almost certainly be client-side evaluation at work inside the Select with the use of these Factory methods. Instead, change it to work explicitly to populate the desired models, and ideally define simplified view models or DTOs to represent the structure you need, with just the columns you need, which can improve indexing options/use and reduce the amount of data and such being composed.

I.e. manually compose the objects rather than using factory methods. EF cannot translate a call to XFactory.Create() into SQL so it will end up potentially fetching a lot of extra data into memory to pass into these factory calls. Instead, use Select to create instances of the classes manually. AFAIK those classes should have a default constructor and setters that EF can access.

Also consider why you might need to return 100k rows. If this is information a user is viewing, consider employing server-side pagination to load paged data so that maybe only 100 or so rows are fetched at a time.

Edit: Regarding navigation properties, these are references from one entity to related ones using one-to-many, one-to-one, or many-to-many relationships. Your entities may already have these set up, for instance your classes already likely have them, as I can see client contains a ClientAddresses, so check if Account has a Client. In this case the query can be re-written as:

var accounts = await _databaseContext.Accounts
   .Where(a => a.Client.EffectiveTo == null
                  && a.Client.ClientType.Equals("PERSON")
                  && a.EffectiveTo == null
                  && a.Status.Equals("INFORCE"))
   .Select(a => new Employee
   {
       Id = a.AccountId,
       ClientId = a.Client.ClientId,
       FirstName = a.Client.GivenNames,
       LastName = a.Client.Surname,
       Gender = a.client.Gender,
       Addresses = a.Client.ClientAddresses
           .Where(ca => ca.EffectiveTo == null)
           .Select(ca => new Address { /* populate from client address */ }).ToList(),
       Contacts = a.Client.ClientContacts
           .Where(c => c.EffectiveTo == null && (c.ContactType == "EMAIL" || c.ContactType == "MOBILE"))
           .OrderBy(c => c.EffectiveFrom)
           .Select(c => new Contact { /* ... */ }).ToList(),
       DateOfBirth = a.Client.DateOfBirth
   }).ToListAsync(cancellationToken);