I have a list of patients, Patient
class has Prescriptions
property, which is a list of Prescription
objects. Prescription
has 3 important attributes, Medicament
,Amount
and Type
(which is basically a key for joining).
I need to join a list of Prescription types, as a prescription type has an attribute Influence
which servers as multiplier for the amount.
var prescriptions = (from prs in
(from p in patients
from pr in p.Prescriptions
join type in DatabaseService.PrescriptionTypes
on pr.Type equals type.Value
select new { Med = pr.Medicament,Amount = pr.Amount * (int)type.Influence })
group prs by prs.Med into prgs
select new { Medicament = prgs.Key,Amount = prgs.Sum(prg => prg.Amount) }).ToList();
Basically what I do (from inner to outer queries)
- Join prescriptions with prescription types, get
Medicament
and the multipliedAmount
- Group the records by the
Medicament
- Run one more select to get the overall sum from all patients
Is there any simpler way?
EDIT:
So I realised couple things and simplified the query quite a bit, I think it's much more readable now. The things I missed the first time were:
SelectMany
that lets me get rid of the outter select, since prescriptions are all I care about- Being able to create new objects within
group
statement, hence one more select can be avoided
The new code looks like
var prescriptions = from p in patients.SelectMany(p => p.Prescriptions)
join type in DatabaseService.PrescriptionTypes
on p.Type equals type.Value
group new { Amount = p.Amount * (int)type.Influence } by p.Medicament into prs
select new { Medicament = prs.Key,Amount = prs.Sum(pr => pr.Amount) };
With that I'm pretty happy and I doubt you can simplify it anymore.