Converting sql query to EF query- nested query in from

Question

Hey guys

Just wondering how the following sql query would look in linq for Entity Framework...

SELECT  KPI.*
FROM    KeyPerformanceIndicator KPI
    INNER JOIN (
        SELECT  SPP.SportProgramPlanId
        FROM    SportProgramPlan PSPP
            INNER JOIN SportProgramPlan ASPP
                ON (PSPP.SportProgramPlanId = @SportProgramPlanId
                    AND PSPP.StartDate >= ASPP.StartDate
                    AND PSPP.EndDate <= ASPP.EndDate)
    ) AS SPP
        ON KPI.SportProgramPlanId = SPP.SportProgramPlanId

Cheers Anthony

Answer 1

Hard to say without seeing the associations in your model. Would there be a self-referential association on SportProgramPlan?

The SQL seems like an error to me as PSPP and ASPP could be the same record, and I'm not sure you want that? At any rate, it's trivial to exclude....

Here's a shot at it:

var q = from kpi in Context.KeyPerformanceIndicators
        where kpi.SportProgramPlan.Id = sportProgramPlanId 
            && Context.SportProgramPlans.Any(aspp => 
                                                 spp.StartDate >= aspp.StartDate
                                                 && spp.EndDate <= aspp.EndDate))
        select ...