Joining composite key entities causes ORA-00923: FROM keyword not found where expected

Joining composite key entities causes ORA-00923: FROM keyword not found where expected

Postby Butzei » Fri 26 May 2017 08:09

Hi,
we are moving from Nhibernate to devart DoConnect for Oracle with Entity Framework Core on an legacy database (oracle 12c). Our query provider is automapper.
Actually we have a problem with a query joining a composite key entity.
Code: Select all
CREATE TABLE TblParameter
   (   "ParameterPosition" NUMBER(10,0),
   "MesurementProgramGroupId" NUMBER(10,0) NOT NULL ENABLE,
         ...)

Code: Select all
CREATE TABLE TblBoxParameterToParameter"
   (   "BoxParameterId" RAW(16) NOT NULL ENABLE,
   "MesurementProgramGroupId" NUMBER(10,0) NOT NULL ENABLE,
   "ParameterPosition" NUMBER(10,0) NOT NULL ENABLE,
   "Position" NUMBER(9,0) NOT NULL ENABLE)
CONSTRAINT "TblBoxParameterToParameter_PK" PRIMARY KEY ("BoxParameterId", "MesurementProgramGroupId", "ParameterPosition") ENABLE,
CONSTRAINT "TblBoxParamToParam_FK" FOREIGN KEY ("MesurementProgramGroupId", "ParameterPosition")
     REFERENCES TblParameter ("MesurementProgramGroupId", "ParameterPosition") ENABLE


So a BoxParameterToParameter references a Parameter by its composite primary key ("MesurementProgramGroupId", "ParameterPosition").
Let AutoMapper join the entities results in the following sql query:
Code: Select all
SELECT "x".POSITION,
     [b]"x".MesurementProgramGroupId IS NOT NULL OR "x".ParameterPosition IS NOT NULL[/b],
     "x.Parameter".MesurementProgramGroupId,
      "x.Parameter".ParameterPosition,
     ...
FROM TblBoxParameterToParameter "x"
INNER JOIN TblParameter "x.Parameter" ON ("x".MesurementProgramGroupId= "x.Parameter".ParameterPosition) AND ("x".ParameterPosition= "x.Parameter".MesurementProgramGroupId)
WHERE "x".BoxParameterId = :p__bpId_0


As you can see there is a confusing line
Code: Select all
"x".MesurementProgramGroupId IS NOT NULL OR "x".ParameterPosition IS NOT NULL
in this statement causing a ORA-00923: FROM keyword not found where expected.

Can anyone tell me how to avoid this problem?

Thanks,
Butzei
Butzei
 
Posts: 12
Joined: Fri 26 May 2017 07:41

Re: Joining composite key entities causes ORA-00923: FROM keyword not found where expected

Postby Shalex » Wed 31 May 2017 16:37

Please upload a small test project with the corresponding DDL/DML script for reproducing to our ftp server (ftp://ftp.devart.com/, credentials: anonymous/anonymous ) or to any file exchange server so that we could download it from there.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

Re: Joining composite key entities causes ORA-00923: FROM keyword not found where expected

Postby Butzei » Thu 01 Jun 2017 13:28

So i found a workaround for this problem.
It seems as AutoMapper somehow adds the nullcheck if I let him figure out the best mapping for the join. So I manually configured the one to one join in the parents mapping configuration.

Thx
Butzei
 
Posts: 12
Joined: Fri 26 May 2017 07:41


Return to dotConnect for Oracle