Devart Forums

Hello,

We have a quite simple LINQ query that results in Oracle error ORA-01747: invalid user.table.column, table.column, or columns specification

Here is the statement:

long seasonId = 6561;

var season = from s in _entities.Seasons

join sst in _entities.SeasonsTitles on seasonId equals sst.SeasonId into sstj
from sst in
sstj.Where(m => m.TitlesGroupType == TitlesGroupType.SecondaryTitle).DefaultIfEmpty()

select s;

And here is generated SQL:

SELECT
"Limit1".SEASON_ID,
"Limit1".PRIORITY,
"Limit1".ACTIVE,
"Limit1".EPISODE_TOTAL,
"Limit1".CREATED,
"Limit1".CHANGED,
"Limit1".MEDIUM,
"Limit1".SEQUENCE_NUMBER,
"Limit1".SERIE_ID,
"Limit1".SEASON_NAME
FROM (
SELECT
"Extent1".SEASON_ID,
"Extent1".PRIORITY,
"Extent1".ACTIVE,
"Extent1".EPISODE_TOTAL,
"Extent1".CREATED,
"Extent1".CHANGED,
"Extent1".MEDIUM,
"Extent1".SEQUENCE_NUMBER,
"Extent1".SERIE_ID,
"Extent1".SEASON_NAME
FROM SEASONS "Extent1"
CROSS JOIN (
SELECT
"SingleRowTable1".,
"Project1".SEASON_ID,
"Project1".TITLES_GROUP_TYPE
FROM (
SELECT
1
FROM DUAL)
"SingleRowTable1"
LEFT OUTER JOIN (
SELECT
"Extent2".SEASON_ID,
"Extent2".TITLES_GROUP_TYPE
FROM SEASONS_TITLES "Extent2"
WHERE (
6561 /* @p__linq__0 */ = "Extent2".SEASON_ID)
AND
(
"Extent2".TITLES_GROUP_TYPE = 'secondaryTitle' /* @p__linq__1 */)
)
"Project1" ON 1 = 1 )
"Join1"
WHERE ROWNUM <= 1
)
"Limit1"

Look at these lines:
CROSS JOIN (
SELECT
"SingleRowTable1".,

Of course it's wrong - no column is specified. Here are the related table definitions:

CREATE TABLE SEASONS
(
SEASON_ID NUMBER(10) NOT NULL,
SERIE_ID NUMBER(10) NOT NULL,
SEQUENCE_NUMBER NUMBER(10) NOT NULL,
SEASON_NAME NVARCHAR2(10) NOT NULL,
EPISODE_TOTAL NUMBER(10) DEFAULT 0,
PRIORITY NUMBER(10) DEFAULT 3 NOT NULL,
ACTIVE NUMBER(1) DEFAULT 1 NOT NULL,
MEDIUM NVARCHAR2(50) NOT NULL,
CREATED DATE NOT NULL,
CHANGED DATE,
SUPPLEMENTAL LOG GROUP GGS_SEASONS_61285 (SEASON_ID) ALWAYS
)
/


CREATE INDEX IDX_SEASONS_MEDIUM ON SEASONS
(MEDIUM)
/


CREATE UNIQUE INDEX IDX_SERIE_SEASON ON SEASONS
(SERIE_ID, SEASON_ID)
/


CREATE OR REPLACE TRIGGER SERIES_SEASONS_INS_TRG
BEFORE INSERT
ON SEASONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT Seasons_SEQ.NEXTVAL INTO :NEW.Season_Id FROM DUAL;
END;
/


ALTER TABLE SEASONS ADD (
PRIMARY KEY
(SEASON_ID))
/

CREATE TABLE SEASONS_TITLES
(
TITLE_ID NUMBER(10) NOT NULL,
SEASON_ID NUMBER(10) NOT NULL,
TITLE NVARCHAR2(255) NOT NULL,
TITLE_TYPE NVARCHAR2(50) NOT NULL,
TITLES_GROUP_TYPE NVARCHAR2(255) NOT NULL,
PURPOSE NVARCHAR2(50),
REFERENCE NVARCHAR2(255),
LINK NVARCHAR2(255),
LABEL NVARCHAR2(255),
RESTRICTION NVARCHAR2(50),
CREATED DATE NOT NULL,
CHANGED DATE,
SUPPLEMENTAL LOG GROUP GGS_SEASONS_TITLES_61293 (TITLE_ID) ALWAYS
)
/


CREATE INDEX IDX_SEASONS_TITLES_SEASONID ON SEASONS_TITLES
(SEASON_ID)
/


CREATE OR REPLACE TRIGGER SEASONSTITLES_INS_TRG
BEFORE INSERT
ON SEASONS_TITLES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT SEASONS_TITLES_SEQ.NEXTVAL INTO :NEW.Title_Id FROM DUAL;
END;
/


ALTER TABLE SEASONS_TITLES ADD (
PRIMARY KEY
(TITLE_ID))
/

ALTER TABLE SEASONS_TITLES ADD (
CONSTRAINT FK_SEASONS_TITLES_SEASON_ID
FOREIGN KEY (SEASON_ID)
REFERENCES SEASONS (SEASON_ID))
/

I believe this used to work few releases back.

Thank you for your report. We have reproduced the problem and are investigating it.

The bug with generating CROSS JOIN statement when using LINQ to Entities is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Great news! Thanks for the fix.

P.S. Any chance to have a look at FunctionImport bug that I posted few days ago?

Oops. Didn't see your reply to that one. You've reproduced that too! Awaiting for the fix

New build of dotConnect for Oracle 6.60.268 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22977 .