Advanced Linq query using into

Question

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.

Answer 1

I figured it out.

var query = from t in dcs.KPIType.Where(t => t.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 = from x in results1 //where x.KPITypeID = t.ID 
           join y in results2
            on x.KPIType.ID equals y.KPIType.ID
           select new {x},   
    KPIHistory = from x in results2
                 join y in results1 
                    on x.KPIType.ID equals y.KPIType.ID
                 select new {x}
};  

In the new, do sub selects that will join the data back on itself so that only results that have data in both KPIHistory and Goal are shown.