Mic îndrumar de LEFT JOIN cu LINQ
Nu sunt rare cazurile în care avem nevoie de un LEFT JOIN prin selecturile de la casa omului şi deoarece LINQ nu are operator LEFT JOIN dedicat, trebuie rezovată problema într-un fel…
Deci, dacă ai nevoie de un SELECT de genul:
SELECT
t0.ID_REC_PROJECT_TARGET AS _targetId, t0.ID_PERSON AS _personId, t0.ID_PROJECT AS _projectId, t5.COMPANY_NAME AS _company,
t4.POSITION AS _position, t2.VALUE AS _priority, t3.VALUE AS _source, t0.COMMENT AS _comments
FROM
dbo.REC_PROJECT_TARGET AS t0
INNER JOIN dbo.PERSON AS t1 ON t0.ID_PERSON = t1.ID_PERSON
INNER JOIN dbo.REC_LOOKUP AS t2 ON t0.ID_PRIORITY = t2.ID_REC_LOOKUP
INNER JOIN dbo.REC_LOOKUP AS t3 ON t0.ID_SOURCE = t3.ID_REC_LOOKUP
LEFT OUTER JOIN dbo.PERSON_WORKEXPERIENCE AS t4 ON (t4.IS_PRESENT = 1) AND (t1.ID_PERSON = t4.ID_PERSON)
LEFT OUTER JOIN dbo.COMPANY AS t5 ON t4.ID_COMPANY = (t5.ID_COMPANY)
El se traduce în LINQ în felul următor:
from t in dac.REC_PROJECT_TARGETs
join p in dac.PERSONs on t.ID_PERSON equals p.ID_PERSON
join lp in dac.REC_LOOKUPs on t.ID_PRIORITY equals lp.ID_REC_LOOKUP
join ls in dac.REC_LOOKUPs on t.ID_SOURCE equals ls.ID_REC_LOOKUP
join wp in dac.PERSON_WORKEXPERIENCEs on p.ID_PERSON equals wp.ID_PERSON into wpl
from wpo in wpl.Where(wpo => wpo.IS_PRESENT).DefaultIfEmpty()
join co in dac.COMPANies on wpo.ID_COMPANY equals co.ID_COMPANY into cpu
from c in cpu.DefaultIfEmpty()
where t.ID_PROJECT == projectId
select new {t.ID_REC_PROJECT_TARGET,
t.ID_PERSON,
t.ID_PROJECT,
c.COMPANY_NAME,
wpo.POSITION,
lp.VALUE,
ls.VALUE,
t.COMMENT}
Partea interesantă (cea cu LEFT JOIN in LINQ) este următoarea:
LEFT OUTER JOIN dbo.PERSON_WORKEXPERIENCE AS t4 ON (t4.IS_PRESENT = 1) AND (t1.ID_PERSON = t4.ID_PERSON)
se traduce:
join wp in dac.PERSON_WORKEXPERIENCEs on p.ID_PERSON equals wp.ID_PERSON into wpl from wpo in wpl.Where(wpo => wpo.IS_PRESENT).DefaultIfEmpty()
Acuma, dacă vrem să complicăm lucrurile puţin, am nevoie sa imi aduc doar acele date care nu au referinte prin alte tabele, adică:
SELECT
t0.ID_REC_PROJECT_TARGET AS _targetId, t0.ID_PERSON AS _personId, t0.ID_PROJECT AS _projectId, t5.COMPANY_NAME AS _company,
t4.POSITION AS _position, t2.VALUE AS _priority, t3.VALUE AS _source, t0.COMMENT AS _comments
FROM dbo.REC_PROJECT_TARGET AS t0
INNER JOIN dbo.PERSON AS t1 ON t0.ID_PERSON = t1.ID_PERSON
INNER JOIN dbo.REC_LOOKUP AS t2 ON t0.ID_PRIORITY = t2.ID_REC_LOOKUP
INNER JOIN dbo.REC_LOOKUP AS t3 ON t0.ID_SOURCE = t3.ID_REC_LOOKUP
LEFT OUTER JOIN dbo.PERSON_WORKEXPERIENCE AS t4 ON (t4.IS_PRESENT = 1) AND (t1.ID_PERSON = t4.ID_PERSON)
LEFT OUTER JOIN dbo.COMPANY AS t5 ON t4.ID_COMPANY = (t5.ID_COMPANY)
LEFT OUTER JOIN dbo.REC_EVENT AS t6 ON (t0.ID_PROJECT = t6.ID_PROJECT) AND (t0.ID_PERSON = t6.ID_PERSON)
LEFT OUTER JOIN dbo.REC_PROJECT_TARGET_REJECTED AS t7 ON t0.ID_REC_PROJECT_TARGET = t7.ID_REC_PROJECT_TARGET
WHERE (t0.ID_PROJECT = @p0) AND (t6.ID_REC_EVENT IS NULL) AND (t7.ID_REC_PROJECT_TARGET_REJECTED IS NULL)
Practic, vreau să elimin din recordsetul meu, înregistrările care apar prin tabelele REC_EVENT respectiv REC_PROJECT_TARGET_REJECTED.
Astfel, construcţia LINQ arată aşa:
from t in dac.REC_PROJECT_TARGETs
join p in dac.PERSONs on t.ID_PERSON equals p.ID_PERSON
join lp in dac.REC_LOOKUPs on t.ID_PRIORITY equals lp.ID_REC_LOOKUP
join ls in dac.REC_LOOKUPs on t.ID_SOURCE equals ls.ID_REC_LOOKUP
join wp in dac.PERSON_WORKEXPERIENCEs on p.ID_PERSON equals wp.ID_PERSON into wpl
from wpo in wpl.Where(wpo => wpo.IS_PRESENT).DefaultIfEmpty()
join co in dac.COMPANies on wpo.ID_COMPANY equals co.ID_COMPANY into cpu
from c in cpu.DefaultIfEmpty()
join re in dac.REC_EVENTs on new { t.ID_PROJECT, t.ID_PERSON } equals new { re.ID_PROJECT, re.ID_PERSON } into tempEvents from events in tempEvents.DefaultIfEmpty()
join rj in dac.REC_PROJECT_TARGET_REJECTEDs on t.ID_REC_PROJECT_TARGET equals rj.ID_REC_PROJECT_TARGET into tempRej from rejected in tempRej.DefaultIfEmpty()
where t.ID_PROJECT == projectId && object.Equals(null, events.ID_REC_EVENT) && object.Equals(null, rejected.ID_REC_PROJECT_TARGET_REJECTED)
select new {t.ID_REC_PROJECT_TARGET,
t.ID_PERSON,
t.ID_PROJECT,
c.COMPANY_NAME,
wpo.POSITION,
lp.VALUE,
ls.VALUE,
t.COMMENT}
Se observă că au aparut cele două tabele in body-ul selectului, însă au apărut şi două elemente noi în clauza WHERE: object.Equals(null, events.ID_REC_EVENT) && object.Equals(null, rejected.ID_REC_PROJECT_TARGET_REJECTED)
Nu am folosit events.ID_REC_EVENT == null deoarece in SQL ar fi arătat aÅŸa: t6.ID_REC_EVENT = NULL ÅŸi sunt sigur că nu era de dorit 🙂
Leave a Reply