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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Butzei
Posts: 12
Joined: Fri 26 May 2017 07:41

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

Post by 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

Shalex
Devart Team
Posts: 8072
Joined: Thu 14 Aug 2008 12:44

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

Post by 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.

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

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

Post by 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

Post Reply