CODEWITHSUNDEEP
sqllinqsql-server-2008entity-framework
Jerrold
Jerrold

Reputation: 1564

How can i do this SQL in Linq? (Left outer join w/ dates)

My LINQ isnt the best, neither is my SQL but im trying to do something like this in LINQ (its kind of in pseudo-code)

select * from CarePlan c 
-- only newest Referral based on r.Date (if more than one exists)
left outer join Referral r on r.CarePlanId = c.CarePlanId 
where c.PatientId = 'E4A1DA8B-F74D-4417-8AC7-B466E3B3FFD0'

The data looks like this:

Would like to return a list of careplans for each patient (whether or not they have a referral or not, if it has more than one referral - grab the newest one)

Thanks for any help guys

Upvotes: 0

Views: 329

Answers (1)

kaj
kaj

Reputation: 5251

In LINQ you use the DefaultIfEmpty to achieve a left outer join - examples at http://msdn.microsoft.com/en-us/library/bb397895.aspx

Assuming that the referrals are not a (potentially empty) collection on the care plans so you're joining two collections together ...

Your query it would be something like:

Get the latest referral per Care Plan: 

var latestReferrals = from r in referrals  
      group r by r.CarePlanId into lr  
      select new { CarePlanId = lr.Key, LatestReferral = lr.OrderByDescending(lrd => lrd.Date).First()};

Find the combined details: 

var q = from c in CarePlan
        where c.PatientId = 'E4A1DA8B-F74D-4417-8AC7-B466E3B3FFD0'
        join lr in latestReferrals on c.CarePlanId equals lr.CarePlanId into gj
        from subReferral in gj.DefaultIfEmpty()
        select new { CarePlanId = c.CarePlanId, LatestReferral = (subReferral == null) ? null : subReferral.LatestReferral };

Depending on whether you want many referral properties or just a few you may or may not want the whole Referral object in the second part or just extract the relevant properties.

You may be able to combine these into a single query but for readability it may be easier to keep them separate. If there is a combined solution you should also compare performance of the two approaches.

EDIT: see comment for joining patients/other tables from care plans

If Patient is joined from Referral (as per comment) then its more complex because you're doing several left outer joins. So switching to the slightly more concise syntax: 

var combined = from c in carePlans  
where c.PatientId = 'E4A1DA8B-F74D-4417-8AC7-B466E3B3FFD0'   
from lr in latestReferral.Where(r => r.CarePlanId == c.CarePlanId).DefaultIfEmpty()   
from p in patients.Where(patient => patient.PatientId == ((lr != null) ? lr.LatestReferral.PatientId : -1)).DefaultIfEmpty()   
select new { c.CarePlanId, PatientName = (p == null) ? "no patient" : p.PatientName, LatestReferral = (lr == null) ? null : lr.LatestReferral };

Upvotes: 1

Related Questions