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

c# entity-framework entity-framework-core linq

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;
    }
}
1
4
8/14/2018 1:30:32 PM

Popular Answer

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.

1
8/14/2018 8:10:59 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow