Writing this SQL in LINQ? (outer apply)

Question

My apologies for my recent SQL/Linq questions, but i need to know what this working SQL query would look like in LINQ?

select *
from CarePlan c
outer apply (select top 1 * from Referral r 
             where
    r.CarePlanId = c.CarePlanId order by r.ReferralDate desc) x  
          left outer join Specialist s on s.SpecialistId = x.SpecialistId   
          left outer join [User] u on u.UserId = s.UserId

This basically retrieves a list of Careplans with the newest Referral (if it exists), then joins the Specialist and User table based on any found Referrals

Thanks

Did you see this http://stackoverflow.com/questions/3014362/c-sharp-outer-apply-in-linq ? — gandarez, Mar 07 '12 at 13:31

score 1 · Answer 1 · answered Mar 07 '12 at 10:48

1

Kind advice: target on what you want to express in the environment of your class model and LINQ, in stead of trying to reproduce SQL. If you do something like

context.CarePlans
  .Select(cp => new { Plan = cp, FirstReferral = cp.Referrals.FirstOrDefault() }

(provided that it matches your context and ignoring ordering and other joins for clarity)

It would basically do what you want, but it may very well translate to an inline subquery, rather than an OUTER APPLY. To the same effect. And the execution plan probably won't differ very much.

answered Mar 07 '12 at 10:48

Gert Arnold

105,341
31
202
291

Indeed. You also need to do an `orderby` on that inner query, so it would actually be `cp.Referrals.OrderByDescending(r => r.ReferralDate).FirstOrDefault()` or `(from r cp.Referrals orderby r.ReferralDate).FirstOrDefault()`. And notice that there is no `join` or `select` in that example because all the fields you want should be available as properties (directly or by traversing the relationship properties) off the CarePlan object. – luksan Mar 07 '12 at 13:00

Writing this SQL in LINQ? (outer apply)

1 Answers1