Converting sql query to EF query- nested query in from

Question

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

Some part of your query is missing -- there are two ( and one ). Perhaps the start of the WHERE clause? — Jay
– Jay, Commented Mar 9, 2010 at 5:31

Craig Stuntz · Accepted Answer · 2010-03-10 14:35:47Z

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

edited Mar 10, 2010 at 14:35

answered Mar 9, 2010 at 14:19

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vdh_ant Over a year ago

I am trying to bring across the query you provided but the 'Any' method isn't a part of SportProgramPlans... any ideas?

vdh_ant Over a year ago

KeyPerformanceIndicator has only 1 SportProgramPlan but 1 SportProgramPlan can have many KeyPerformanceIndicators...

Craig Stuntz Over a year ago

OK; updated for that cardinality, but see the note; I'm not sure your original SQL is right.

Collectives™ on Stack Overflow

Converting sql query to EF query- nested query in from

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related