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 