So first of all, why is the data not filled? You should be able to fill out the data in any related entities during the actual Car fetch from the DB using .Include.

var carEntity = _context.Cars.Where(predicate).Include(c => c.PolicySummaries).Single();

Secondly, your code suffers from a serious performance issue: policyData is an IQueryable. Every time you do policyData.Single, you send a new query to the database, look through all policies, filter the ones that have their id in policyIds and choose the appropriate one. This will be incredibly inefficient if there are many policies - every foreach iteration is a new query! What you should do is probably do something like:

var policyData = _context.PolicySummary.Where(t => policyIds.Contains(t.PolicyId)).ToList();

To fetch all related policies. This could also be inefficient if there are a lot of shared policies and you end up loading most of them at some point anyway, so if there's not like a billion policies in the DB this:

var policies = _context.PolicySummary.ToList();

could work even better. It's a time/memory tradeoff at this point, but please note that any of the above will be better than the current query-overload foreach you have.


Because you already have existed DTO objects(not entities), you will not be able to effectively use of Join with EntityFramework.
You need to load missing data and then update existed car objects with it.

I think simpler way will be replace list of policies with objects retrieved from database.

var allPolicies = _context.PolicySummary
                          .Where(policy => policyIds.Contains(policy.PolicyId))
                          .Select(group => new PolicySummaryDTO
                              PolicyId = group.Key,
                              PolicyName = group.First().PolicyName,
                              Amount = group.First().Amount                  
                          .ToDictionary(policy => policy.PolicyId);

foreach (var car in cars)
    car.Policies = car.Policies.Select(p => p.PolicyId)
                               .Select(policyId => allPolicies[policyId])

Using Dictionary will keep updating code simpler and reduce amount of operations to execute, less loops.

Note: approach above assumes that all policies Id's exist in the database. If not, these will be removed from the correspondent car.Policies property. You can remove Where(allPolicies.ContainsKey) line and an exception will be thrown if some policies are missing.

Related Articles