This question is locked. New answers and comments are not allowed.
I have the followign Linq query:
List<LogMaster> logMasterList = (from j in qJuvenile join sswl in qSSW_Logs on j.ID equals sswl.JUVENILE_KEY join sswp in _DB.SSW_PARTIEs on sswl.ID equals sswp.SSW_LOG_KEY join sswdt in qSSW_Document_Types on sswp.SSW_DOCUMENT_TYPE_KEY equals sswdt.ID join sswpsx in qSSW_Party_Service_Xref on sswp.ID equals sswpsx.SSW_PARTY_KEY join ssws in qSSW_Statuses on sswpsx.SSW_STATUS_KEY equals ssws.ID join sswst in _DB.SSW_SERVICE_TYPEs on sswpsx.SSW_SERVICE_TYPE_KEY equals sswst.ID join sswr in _DB.SSW_REASONs on sswl.ID equals sswr.SSW_LOG_KEY join LOJi in _DB.INCIDENTs on sswr.INCIDENT_KEY equals LOJi.ID into Ti from i in Ti.DefaultIfEmpty() join LOJm in _DB.MOTIONs on sswr.MOTION_KEY equals LOJm.ID into Tm from m in Tm.DefaultIfEmpty() join LOJc in qComplaints on (sswr.INCIDENT_KEY > 0 ? i.COMPLAINT_KEY : m.COMPLAINT_KEY) equals LOJc.ID into Tc from c in Tc.DefaultIfEmpty() join ls in _DB.LOCK_STATUSEs on c.LOCK_STATUS_KEY equals ls.ID join LOJp in _DB.People on (sswp.PARTY_TYPE == 'P' ? sswp.PARTY_KEY : 0) equals LOJp.ID into Tp from p in Tp.DefaultIfEmpty() join LOJjpx in _DB.JUVENILE_PERSON_XREFs on p.ID equals LOJjpx.JUVENILE_KEY into Tjpx from jpx in Tjpx.DefaultIfEmpty() join LOJrtj in _DB.RELATION_TO_JUVENILEs on jpx.RELATION_KEY equals LOJrtj.ID into Trtj from rtj in Trtj.DefaultIfEmpty() join LOJip in _DB.INTERESTED_PARTIEs on (sswp.PARTY_TYPE == 'I' ? sswp.PARTY_KEY : 0) equals LOJip.ID into Tip from ip in Tip.DefaultIfEmpty() join LOJipt in _DB.INTERESTED_PARTY_TYPEs on ip.INTERESTED_PARTY_TYPE_KEY equals LOJipt.ID into Tipt from ipt in Tipt.DefaultIfEmpty() join LOJa in _DB.ATTORNEYs on (sswp.PARTY_TYPE == 'A' ? sswp.PARTY_KEY : 0) equals LOJa.ID into Ta from a in Ta.DefaultIfEmpty() join LOJac in _DB.ATTORNEY_CASEs on new { A = a.ID, B = c.ID } equals new { A = (LOJac.ATTORNEY_KEY == null ? 0 : (long)LOJac.ATTORNEY_KEY), B = (LOJac.COMPLAINT_KEY == null ? 0 : (long)LOJac.ATTORNEY_KEY) } into Tac from ac in Tac.DefaultIfEmpty() join LOJat in _DB.ATTORNEY_TYPEs on ac.ATTORNEY_TYPE_KEY equals LOJat.ID into Tat from at in Tat.DefaultIfEmpty() where sswl.JUVENILE_KEY > 0 select new LogMaster { JUV_NAME = new Name(j.F_NAME, j.M_NAME, j.L_NAME, j.NAME_SUFFIX), JUV_KEY = j.ID, JUV_DOB = j.BIRTH_DATE, PARTY_TYPE = sswp.PARTY_TYPE, PARTY_KEY = sswp.PARTY_KEY, DOC_TYPE_KEY = sswdt.ID, DOC_NAME = sswdt.SSW_DOCUMENT_NAME, SERVICE_TYPE_KEY = sswpsx.SSW_SERVICE_TYPE_KEY, STATUS_KEY = sswpsx.SSW_STATUS_KEY, STATUS_DATE = sswpsx.STATUS_DATE, SERVICE_TYPE = sswst.SSW_SERVICE_TYPE, LOG_KEY = sswl.ID, SSW_PARTY_KEY = sswp.PARTY_KEY, STATUS_CATEGORY = ssws.STATUS_CATEGORY, SSW_PARTY_SERVICE_XREF_KEY = sswpsx.ID, PARTY_NAME = new Name((sswp.PARTY_TYPE == 'J' ? j.F_NAME : sswp.PARTY_TYPE == 'I' ? ip.F_NAME : sswp.PARTY_TYPE == 'A' ? a.F_NAME : " "), (sswp.PARTY_TYPE == 'J' ? j.M_NAME : sswp.PARTY_TYPE == 'I' ? ip.M_NAME : sswp.PARTY_TYPE == 'A' ? a.M_NAME : " "), (sswp.PARTY_TYPE == 'J' ? j.L_NAME : sswp.PARTY_TYPE == 'I' ? ip.L_NAME : sswp.PARTY_TYPE == 'A' ? a.L_NAME : " "), (sswp.PARTY_TYPE == 'J' ? j.NAME_SUFFIX : sswp.PARTY_TYPE == 'I' ? ip.NAME_SUFFIX : sswp.PARTY_TYPE == 'A' ? a.NAME_SUFFIX : " ")), PARTY_COMPANY = (sswp.PARTY_TYPE == 'I' ? ip.COMPANY : sswp.PARTY_TYPE == 'A' ? a.ATTORNEY_FIRM : " "), PARTY_REFERENCE = (sswp.PARTY_TYPE == 'J' ? (j.RECORD_TYPE == "JUV" ? "JUVENILE" : j.RECORD_TYPE == "ADULT" ? "ADULT OFFENDER" : " ") : sswp.PARTY_TYPE == 'P' ? rtj.DESCRIPTION : sswp.PARTY_TYPE == 'A' ? at.DESCRIPTION : " "), IM_KEY = (sswr.INCIDENT_KEY > 0 ? sswr.INCIDENT_KEY : sswr.MOTION_KEY), IM_TYPE = (sswr.INCIDENT_KEY > 0 ? "I" : "M"), COMPLAINT_KEY = c.ID, CASE_NUMBER = c.CASE_NUMBER, IM_COUNT = (sswr.INCIDENT_KEY > 0 ? i.CHARGE_COUNT : m.MOTION_COUNT) }).ToList();
The generated sql is ALMOST correct... for some reason it moves the join to MOTIONS down a couple of spots, so that it is after COMPLAINTS. It causes this error: "Error executing query: Telerik.OpenAccess.RT.sql.SQLException: ORA-00904: "R"."COMPLAINT_KEY": invalid identifier ". See generated query below:
SELECT a."FIRST_NAME" COL1, a."MIDDLE_NAME" COL2, a."LAST_NAME" COL3, a."NAME_SUFFIX" COL4, a."JUVENILE_KEY" COL5, a."BIRTH_DATE" COL6, c."PARTY_TYPE" COL7, c."PARTY_KEY" COL8, d."SSW_DOCUMENT_TYPE_KEY" COL9, d."SSW_DOCUMENT_NAME" COL10, e."SSW_SERVICE_TYPE_KEY" COL11, e."SSW_STATUS_KEY" COL12, e."STATUS_DATE" COL13, g."SSW_SERVICE_TYPE" COL14, b."SSW_LOG_KEY" COL15, f."STATUS_CATEGORY" COL16, e."SSW_PARTY_SERVICE_XREF_KEY" COL17, ASCII(c."PARTY_TYPE") EXPR18, k."FNAME" COL19, m."ATTORNEY_FNAME" COL20, k."MNAME" COL21, m."ATTORNEY_MNAME" COL22, k."LNAME" COL23, m."ATTORNEY_LNAME" COL24, k."SUFFIX" COL25, m."ATTORNEY_SUFFIX" COL26, k."COMPANY" COL27, m."ATTORNEY_FIRM" COL28, a."RECORD_TYPE" COL29, j."RELATE_TO_JUV" COL30, t."ATTORNEY_TYPE_DESCRIPTION" COL31, n."INCIDENT_KEY" COL32, n."MOTION_KEY" COL33, p."COMPLAINT_KEY" COL34, p."CASE_NUMBER" COL35, o."CHARGE_COUNT" COL36, r."MOTION_COUNT" COL37 FROM "JUVENILE" a JOIN "SSW_LOGS" b ON (a."JUVENILE_KEY" = b."JUVENILE_KEY") JOIN "SSW_PARTIES" c ON (b."SSW_LOG_KEY" = c."SSW_LOG_KEY") JOIN "SSW_DOCUMENT_TYPES" d ON (c."SSW_DOCUMENT_TYPE_KEY" = d."SSW_DOCUMENT_TYPE_KEY") JOIN "SSW_PARTY_SERVICE_XREF" e ON (c."SSW_PARTY_KEY" = e."SSW_PARTY_KEY") JOIN "SSW_STATUSES" f ON (e."SSW_STATUS_KEY" = f."SSW_STATUS_KEY") JOIN "SSW_SERVICE_TYPES" g ON (e."SSW_SERVICE_TYPE_KEY" = g."SSW_SERVICE_TYPE_KEY") LEFT JOIN "PERSON" h ON ( (CASE WHEN ASCII(c."PARTY_TYPE") = ? THEN c."PARTY_KEY" ELSE ? END) = h."PERSON_KEY") LEFT JOIN "JUVENILE_PERSON_XREF" i ON (h."PERSON_KEY" = i."JUVENILE_KEY") LEFT JOIN "RELATION_TO_JUVENILE" j ON (i."RELATION_KEY" = j."RELATION_KEY") LEFT JOIN "INTERESTED_PARTIES" k ON ( (CASE WHEN ASCII(c."PARTY_TYPE") = ? THEN c."PARTY_KEY" ELSE ? END) = k."INTERESTED_PARTIES_KEY") LEFT JOIN "INTERESTED_PARTY_TYPES" l ON (k."INTERESTED_PARTY_TYPE_KEY" = l."INTERESTED_PARTY_TYPE_KEY") LEFT JOIN "ATTORNEYS" m ON ( (CASE WHEN ASCII(c."PARTY_TYPE") = ? THEN c."PARTY_KEY" ELSE ? END) = m."ATTORNEY_KEY") JOIN "SSW_REASONS" n ON (b."SSW_LOG_KEY" = n."SSW_LOG_KEY") LEFT JOIN "INCIDENTS" o ON (n."INCIDENT_KEY" = o."INCIDENT_KEY") LEFT JOIN "COMPLAINTS" p ON ( (CASE WHEN n."INCIDENT_KEY" > 0 THEN o."COMPLAINT_KEY" ELSE r."COMPLAINT_KEY" END) = p."COMPLAINT_KEY") JOIN "LOCK_STATUSES" q ON (p."LOCK_STATUS_KEY" = q."LOCK_STATUS_KEY") LEFT JOIN "MOTIONS" r ON (n."MOTION_KEY" = r."MOTION_KEY") LEFT JOIN "ATTORNEY_CASES" s ON (m."ATTORNEY_KEY" = (CASE WHEN s."ATTORNEY_KEY" IS NULL THEN 0 ELSE s."ATTORNEY_KEY" END) and p."COMPLAINT_KEY" = (CASE WHEN s."COMPLAINT_KEY" IS NULL THEN 0 ELSE s."ATTORNEY_KEY" END)) LEFT JOIN "ATTORNEY_TYPES" t ON (s."ATTORNEY_TYPE_KEY" = t."ATTORNEY_TYPE_KEY") WHERE b."JUVENILE_KEY" IS NOT NULL AND (b."JUVENILE_KEY" IN (NULL) AND (1=0)) AND f."STATUS_CATEGORY" <> ? AND b."JUVENILE_KEY" > ?
If I alter the raw sql and drop COMPLAINTS and LOCK_STATUSES down below MOTIONS, then it works just fine. How can I convince it to compile my LINQ query this correctly?
We are using Telerik.OpenAccess v 2015.3.926.1.
-Steven