How to fill data in nested Lists based on Ids in C#?

Question

public class CarDTO
{
    public int CarId { get; set; }
    public string CarName { get; set; }
    public List<PolicySummaryDTO> PolicySummaries { get; set; } 
}

public class PolicySummaryDTO
{
    public long PolicyId { get; set; }
    public string PolicyName { get; set; }
    public decimal Amount { get; set; }
}

I have List<CarDTO> Cars each car has already list of policies List<PolicySummaryDTO> PolicySummaries. PolicyId is filled. But other properties like PolicyName, Amount, etc. are not. The thing I need to do is get data from DB _contex.PolicySummary and I need to complete missing fields.

I know that I could do this in this way.

1. Get all PolicyIds var policyIds = cars.SelectMany(t => t.PolicySummaries).Select(r => r.PolicyId);

2. Get PolicyData based on Ids var policyData = _context.PolicySummary.Where(t => policyIds.Contains(t.PolicyId));

3. And then using foreach I can fill data.

   foreach (var car in cars)
   {
       foreach (var policy in car.PolicySummaries)
       {
           var policyDataToUse = policyData.Single(t => t.PolicyId == policy.PolicyId);
           policy.Amount = policyDataToUse.Amount;
           policy.PolicyName = policyDataToUse.PolicyName;
       }
   }
   

Everthing will works fine, but I wondering whether I can do it in more elegant way, maybe using somehow LINQ JOIN or something, or maybe my solution is totally correct?

---

EDIT - solution with dictionary

var policyIds = cars.SelectMany(t => t.PolicySummaries).Select(r => r.PolicyId);
var policyDict = _context.PolicySummary.Where(t => policyIds.Contains(t.PolicyId)).ToDictionary(t => t.PolicyId);

foreach (var car in cars)
{
    foreach (var policy in car.PolicySummaries)
    {
        var policyDataToUse = policyDict[policy.PolicyId];
        policy.Amount = policyDataToUse.Amount;
        policy.PolicyName = policyDataToUse.PolicyName;
    }
}

Answer 1

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.

Answer 2

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)
                               .Where(allPolicies.ContainsKey)
                               .Select(policyId => allPolicies[policyId])
                               .ToList();
}

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.