CODEWITHSUNDEEP
c#.netsqllinqentity-framework
vdh_ant
vdh_ant

Reputation: 13156

Converting sql query to EF query- nested query in from

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

Upvotes: 0

Views: 410

Answers (1)

Craig Stuntz
Craig Stuntz

Reputation: 126547

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 ...

Upvotes: 1

Related Questions