This query runs fine

from r in Risk
select new 
{
r.OverallControlEffectivenessID
}

as

SELECT "LPLA_1"."OVERAL_CONTROL_EFECTIVENES_ID" AS "F__0"
FROM
 "AQD"."RM_RISK" "LPLA_1"

(my capturing of the sql might be off) but add something to force a nested query like so

from r in Risk
let h = r.Hazard
select new 
{
r.OverallControlEffectivenessID
}

results in

SELECT "LPA_L1"."OverallControlEffectivenessID"
FROM
 (SELECT "LPLA_1"."BUSINESS_UNIT_AFFECTED_ID" AS "BusinessUnitID",
  "LPLA_1"."DATE_IDENTIFIED" AS "DateIdentified",
  "LPLA_1"."ENTERED_BY_ID" AS "EnteredByID",
  "LPLA_1"."ENTERED_ON" AS "EnteredOn",
  "LPLA_1"."EQUIPMENT_INVOLVED" AS "EquipmentInvolved",
  "LPLA_1"."EVENTS_AND_DEVELOPMENTS" AS "EventsAndDevelopments",
  "LPLA_1"."FEEDBACK" AS "Feedback",
  "LPLA_1"."FEEDBACK_BY_ID" AS "FeedbackByID",
  "LPLA_1"."FEEDBACK_ENTERED_ON" AS "FeedbackEnteredOn",
  "LPLA_1"."FINDING_NO" AS "FindingNo",
  "LPLA_1"."HAZARD_NO" AS "HazardNo",
  "LPLA_1"."LAST_REVIEWED_BY_ID" AS "LastReviewedByID",
  "LPLA_1"."LAST_REVIEWED_DATE" AS "LastReviewedDate",
  "LPLA_1"."NEXT_REVIEW_DATE" AS "NextReviewDate",
  "LPLA_1"."OCCURRENCE_NO" AS "OccurrenceNo",
  "LPLA_1"."OVERAL_CONTROL_EFECTIVENES_ID" AS "F__15",
  "LPLA_1"."POTENTIAL_CURRENT_ID" AS "PotentialCurrentID",
  "LPLA_1"."RISK_CATEGORY_ID" AS "RiskCategoryID",
  "LPLA_1"."RISK_DESCRIPTION" AS "RiskDescription",
  "LPLA_1"."RISK_NO" AS "RiskNo",
  "LPLA_1"."RISK_OWNER" AS "RiskOwner",
  "LPLA_1"."RISK_STATUS_ID" AS "RiskStatusID",
  "LPLA_1"."RISK_TITLE" AS "RiskTitle",
  "LPLA_1"."RISK_TREND_COMMENTARY" AS "RiskTrendCommentary",
  "LPLA_1"."RISK_TREND_ID" AS "RiskTrendID",
  "LPLA_1"."RISK_TYPE_ID" AS "RiskTypeID",
  "LPLA_1"."WR_NUMBER" AS "WRNumber",
  "LPLA_1"."WR_PERIOD" AS "WRPeriod",
  "LPLA_1"."WR_TYPE" AS "WRType",
  1 AS "LPFA_3"
FROM
 "AQD"."RM_RISK" "LPLA_1") "LPA_L1"

which results in an ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ORA-00904: "LPA_L1"."OverallControlEffectivenessID": invalid identifier. Check Inne...

Version 3.0.11.125 - Let me know what other info you need.

Can't reproduce your exception.

RTL 3.0.11.125

ODP.Net 2.111.7.20

Code

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = from e in metaData.Employee
            let em = e.Email
            select new {e.EmployeeId};

    var result = q.ToList();
}

Generated SQL

SELECT 
    "LPA_L1"."EmployeeId" 

FROM 
    ( SELECT 
        "LPLA_1"."COMMISSION_PCT" AS "CommissionPct", 
        "LPLA_1"."DEPARTMENT_ID" AS "DepartmentId", 
        "LPLA_1"."EMAIL" AS "Email", 
        "LPLA_1"."EMPLOYEE_ID" AS "EmployeeId", 
        "LPLA_1"."FIRST_NAME" AS "FirstName", 
        "LPLA_1"."HIRE_DATE" AS "HireDate", 
        "LPLA_1"."JOB_ID" AS "JobId", 
        "LPLA_1"."LAST_NAME" AS "LastName", 
        "LPLA_1"."MANAGER_ID" AS "ManagerId", 
        "LPLA_1"."PHONE_NUMBER" AS "PhoneNumber", 
        "LPLA_1"."SALARY" AS "Salary", 
        "LPLA_1"."EMAIL" AS "em" FROM "HR"."EMPLOYEES" "LPLA_1"
    ) "LPA_L1"

Can you give us a tiny repro solution for this? (your LLBLGen project file, the DDL script and some reproduce code, all in a zipped file, without dlls).

daelmo wrote:

Can't reproduce your exception.

RTL 3.0.11.125

ODP.Net 2.111.7.20

Code

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = from e in metaData.Employee
            let em = e.Email
            select new {e.EmployeeId};

    var result = q.ToList();
}

Generated SQL

SELECT 
    "LPA_L1"."EmployeeId" 

FROM 
    ( SELECT 
        "LPLA_1"."COMMISSION_PCT" AS "CommissionPct", 
        "LPLA_1"."DEPARTMENT_ID" AS "DepartmentId", 
        "LPLA_1"."EMAIL" AS "Email", 
        "LPLA_1"."EMPLOYEE_ID" AS "EmployeeId", 
        "LPLA_1"."FIRST_NAME" AS "FirstName", 
        "LPLA_1"."HIRE_DATE" AS "HireDate", 
        "LPLA_1"."JOB_ID" AS "JobId", 
        "LPLA_1"."LAST_NAME" AS "LastName", 
        "LPLA_1"."MANAGER_ID" AS "ManagerId", 
        "LPLA_1"."PHONE_NUMBER" AS "PhoneNumber", 
        "LPLA_1"."SALARY" AS "Salary", 
        "LPLA_1"."EMAIL" AS "em" FROM "HR"."EMPLOYEES" "LPLA_1"
    ) "LPA_L1"

Can you give us a tiny repro solution for this? (your LLBLGen project file, the DDL script and some reproduce code, all in a zipped file, without dlls).

If you add a 30 char name column to the EMPLOYEES table such as 'OVERAL_CONTROL_EFECTIVENES_ID' it should happen. If not I'll attach what you ask. ODP.Net 2.112.1.2

Would you please attach a repr0 solution? (make it as small and simple as possible). Thanks.

I think I can repro this with the test databases we have here. I'll see if I can make it throw up. Seems like some logic is missing to re-alias the field as well, although renames of fields in derived tables are picked up, which is the same thing... (David's query)

(edit) I think it's caused by the fact that the alias the outer query refers to is < 30 chars. It is referring to the right field, but when the inner query's field is emitted into the SQL, the field length is checked, not the alias length. The field length is 30 chars and re-aliased. This is not really OK, as the field length is apparently OK (as it's defined in the DB after all ). It should only check aliases or fields targeting derived tables when re-aliasing fields. If the field name in the entity is also > 30 chars it's likely to work properly. (not tested yet)

Reproduced.

The check was OK (for 27 chars) but it didn't take into account the situation where the alias was shorter than the actual field name. I.o.w.: it doesn't need to shorten if the field name is actually longer or equal.

When you use an entity field name with a length > 31 chars it might go wrong still, in the second situation as the name of the field targeting the derived table isn't shortened, as it doesn't know the field to shorten to (as the shortening happens at the last minute).

Thanks