Advanced Linq query using into
- by dilbert789
I have this query that someone else wrote, it's over my head so I'm looking for some direction. What is happening currently is that it's taking numbers where there is a goal and no history entered, or history and no goal, this screws up the calculations as both goal and history for the same item are required on each.
The three tables involved are:
KPIType
Goal
KPIHistory
What I need:
Need all rows from KPIType.
Need all goals where there is a matching KPIHistory row (Goal.KPItypeID == KPIHistory.KPItypeID ) into results 1
Need all kpiHistory’s where there is a matching Goal row (Goal.KPItypeID == KPIHistory.KPItypeID ) into results 2
Current query:
var query = from t in dcs.KPIType.Where(k => k.ID <= 23)
join g in dcs.Goal.Where(g => g.Dealership.ID == dealershipID
&& g.YearMonth >= beginDate && g.YearMonth <= endDate
)
on t.ID equals g.KPITypeID into results1
join h in dcs.KPIHistory.Where(h => h.Dealership.ID == dealershipID
&& h.ForDate >= beginDate && h.ForDate <= endDate
)
on t.ID equals h.KPIType.ID into results2
orderby t.DisplayOrder
select new { t, Goal = results1, KPIHistory = results2 };
query.ToList().ForEach(q =>
{
results.Add(q.t);
});
Thanks, I'm happy to answer questions if more info needed.
