Join in wrong order

Thread is closed for posting
1 posts, 0 answers
  1. Steven
    Steven avatar
    5 posts
    Member since:
    Dec 2013

    Posted 04 May 2017 Link to this post

     

    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

     

     

     

Back to Top