Bad SQL generated through LINQ to Oracle

[crazypit]

Hello,

Using the latest LinqConnect version, when i try to run the following LINQ:

var activationData = context.Query(
@"SELECT SupplyNo, ActivationDate, Processed AS Processed_Str, Success AS Success_Str, Message
FROM ORDER_ACTIVATION_DATES WHERE Processed = '0' OR Success = '0'");

var data = from objActData in activationData
join objSivLeft in context.ServiceIdentifierValues on objActData.SupplyNo equals
objSivLeft.ValueFrom into sivs
from objSiv in sivs.DefaultIfEmpty()
join objContractServiceLeft in context.ContractServices on objSiv.CsId equals
objContractServiceLeft.Id into cs
from objContractService in cs.DefaultIfEmpty()
join objContractProductLeft in context.ContractProducts on objContractService.CpId equals
objContractProductLeft.Id into cp
from objContractProduct in cp.DefaultIfEmpty()
join objActCaseLeft in context.Cases on new { CtId = 11L, RefScopeId = objContractProduct.Id }
equals new { objActCaseLeft.CtId, objActCaseLeft.RefScopeId } into actCases
from objActCase in actCases.DefaultIfEmpty()
select new
{
CurEvent = context.Events.Where(a => a.CaId == objActCase.Id).OrderByDescending(a => a.Id).FirstOrDefault(),
ActCase = objActCase,
Siv = objSiv,
Activation = objActData
};
var result = data.ToList();

I get a produced SQL that gives me an ORA-00933 error:

SELECT t6.EV_ID, t6.CA_ID, t6.ET_ID, t6.WR_ID, t6.EV_STATUS, t6.DATF, t6.DATT, t6.EV_MEMO, t6.EV_RESULT, t6.EV_ACT_STATUS, t6.CRUSER, t6.CRD, t6.CHUSER, t6.CHD, t6.CRAPPUSER, t6.CRAPPD, t6.CHAPPUSER, t6.CHAPPD, t6.EV_PRIORITY, t5.CA_ID AS CA_ID1, t5.CU_ID, t5.CT_ID, t5.CA_STATUS, t5.CA_PAR_ID, t5.CA_RESULT, t5.WDV_ID, t5.WR_ID_ENTRY, t5.WR_ID_EXIT, t5.CA_REF_SCOPE_ID, t5.CA_ACT_STATUS, t5.CRUSER AS CRUSER1, t5.CRD AS CRD1, t5.CHUSER AS CHUSER1, t5.CHD AS CHD1, t5.CRAPPUSER AS CRAPPUSER1, t5.CRAPPD AS CRAPPD1, t5.CHAPPUSER AS CHAPPUSER1, t5.CHAPPD AS CHAPPD1, t5.CA_PRIORITY, t5.DATF AS DATF1, t5.DATT AS DATT1, t2.SIV_ID, t2.SI_ID, t2.SU_ID, t2.CS_ID, t2.SIV_VALUE_FROM, t2.SIV_VALUE_TO, t2.SIV_ACT_DATE, t2.SIV_DEACT_DATE, t2.SIV_ACTION, t2.CU_ID AS CU_ID1, t2.CRUSER AS CRUSER2, t2.CRD AS CRD2, t2.CHUSER AS CHUSER2, t2.CHD AS CHD2, t2.SIV_ALLOW_EDIT, t1.SupplyNo AS "SupplyNo", t1.ActivationDate AS "ActivationDate", t1.Processed_Str AS "Processed_Str", t1.Success_Str AS "Success_Str", t1.Message AS "Message", t1.Processed AS "Processed", t1.Success AS "Success"
FROM (
SELECT SupplyNo, ActivationDate, Processed AS Processed_Str, Success AS Success_Str, Message
FROM ORDER_ACTIVATION_DATES WHERE Processed = '0' OR Success = '0'
) t1
LEFT OUTER JOIN SERV_IDENTIFIERS_VALUES t2 ON t1.SupplyNo = t2.SIV_VALUE_FROM
LEFT OUTER JOIN CONTRACT_SERVICES t3 ON t2.CS_ID = t3.CS_ID
LEFT OUTER JOIN CONTRACT_PRODUCTS t4 ON t3.CP_ID = t4.CP_ID
LEFT OUTER JOIN CASES t5 ON (:p0 = t5.CT_ID) AND (t4.CP_ID = t5.CA_REF_SCOPE_ID)
OUTER APPLY (
SELECT t7.EV_ID, t7.CA_ID, t7.ET_ID, t7.WR_ID, t7.EV_STATUS, t7.DATF, t7.DATT, t7.EV_MEMO, t7.EV_RESULT, t7.EV_ACT_STATUS, t7.CRUSER, t7.CRD, t7.CHUSER, t7.CHD, t7.CRAPPUSER, t7.CRAPPD, t7.CHAPPUSER, t7.CHAPPD, t7.EV_PRIORITY, ROW_NUMBER() OVER (ORDER BY t7.EV_ID DESC) AS "rnum"
FROM (
SELECT t8.EV_ID, t8.CA_ID, t8.ET_ID, t8.WR_ID, t8.EV_STATUS, t8.DATF, t8.DATT, t8.EV_MEMO, t8.EV_RESULT, t8.EV_ACT_STATUS, t8.CRUSER, t8.CRD, t8.CHUSER, t8.CHD, t8.CRAPPUSER, t8.CRAPPD, t8.CHAPPUSER, t8.CHAPPD, t8.EV_PRIORITY
FROM EVENTS t8
WHERE t8.CA_ID = t5.CA_ID
ORDER BY t8.EV_ID DESC
) t7
) t6
WHERE t6."rnum" <= 1

SQL command brakes just before the OUTER APPLY part. I tested the query on 10.2 & 11.2 systems.

[StanislavK]

Thank you for the report, we have reproduced the issue. We will investigate it and inform you about the results as soon as possible.

[crazypit]

Hello,

anything new on that? It's been over a month.

[StanislavK]

We are working on this issue, but cannot provide any timeframe at the moment. We will post here when any new information about the problem with outer apply is available.