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.
Get all PolicyIds
var policyIds = cars.SelectMany(t => t.PolicySummaries).Select(r => r.PolicyId);
Get PolicyData based on Ids
var policyData = _context.PolicySummary.Where(t => policyIds.Contains(t.PolicyId));
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;
}
}
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.