TPT Query not returning expected results

[nicolas.bourgoin]

Hi,

I've been using dotConnect for Oracle for a bit now. I am trying to use it on an already existing database that has table inheritance on which I have no control. I have 5 classes mapped:

Job, Element, Cable, Splice, Terminal.

A job has many elements.
Cable, Splice and Terminal ARE elements (inheritance).
An element is not abstract because it can be something else than one of the above.

I am simply trying to query my elements to get a list of elements (which would result in a list of cables, splices and terminals).

Here's a simple query I am running:

Code: Select all

//My _elementrepository.query is simply a IQueryable<Element> that includes Job
                var elementsQuery = _elementRepository.Query(x => x.Job)
                    .Where(x => x is Cable || x is Splice || x is Terminal)
                    .Where(x => x.JOB_ID == job.G3E_IDENTIFIER);

                Debug.Write(elementsQuery.ToString());

                var elements = elementsQuery.ToList();

Here is the SQL Generated:

Code: Select all

SELECT 
"Extent1".G3E_FID,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN '0X' WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN '0X0X' WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN '0X1X' ELSE '0X2X' END AS C1,
"Extent1".G3E_ID,
"Extent1".G3E_FNO,
"Extent1".JOB_ID,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN "Project3".DIAMETER WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN TO_NUMBER(NULL) END AS C2,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN "Project3".TOTAL_LENGTH WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN TO_NUMBER(NULL) END AS C3,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN "Project3".TOTAL_SIZE WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN TO_NUMBER(NULL) END AS C4,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN "Project3".GAUGE WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN TO_NUMBER(NULL) END AS C5,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN TO_NUMBER(NULL) WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN "Project2".SPLICE_LOSS END AS C6,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN TO_NUMBER(NULL) WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN TO_NUMBER(NULL) ELSE "Project1".TERMINAL_SYS_ID END AS C7,
CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN TO_NUMBER(NULL) WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN TO_NUMBER(NULL) ELSE "Project1".TOTAL_SIZE END AS C8,
"Extent5".G3E_IDENTIFIER,
"Extent5".WORK_ORDER_ID,
"Extent5".G3E_PLACED,
"Extent5".COMPANY_OWNERSHIP
FROM     GCOMM.GC_NETELEM "Extent1"
LEFT OUTER JOIN  (SELECT 
	"Extent2".G3E_FID,
	"Extent2".TERMINAL_SYS_ID,
	"Extent2".TOTAL_SIZE,
	1 AS C1
	FROM GCOMM.GC_TERM "Extent2" ) "Project1" ON "Extent1".G3E_FID = "Project1".G3E_FID
LEFT OUTER JOIN  (SELECT 
	"Extent3".G3E_FID,
	"Extent3".SPLICE_LOSS,
	1 AS C1
	FROM GCOMM.GC_SPLICE "Extent3" ) "Project2" ON "Extent1".G3E_FID = "Project2".G3E_FID
LEFT OUTER JOIN  (SELECT 
	"Extent4".G3E_FID,
	"Extent4".DIAMETER,
	"Extent4".TOTAL_LENGTH,
	"Extent4".TOTAL_SIZE,
	"Extent4".GAUGE,
	1 AS C1
	FROM GCOMM.GC_CBL "Extent4" ) "Project3" ON "Extent1".G3E_FID = "Project3".G3E_FID
LEFT OUTER JOIN GCOMM.G3E_JOB "Extent5" ON "Extent1".JOB_ID = "Extent5".G3E_IDENTIFIER
WHERE (((CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN '0X' WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN '0X0X' WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN '0X1X' ELSE '0X2X' END LIKE '0X0X%') OR (CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN '0X' WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN '0X0X' WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN '0X1X' ELSE '0X2X' END LIKE '0X1X%')) OR (CASE WHEN (( NOT (("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) AND ( NOT (("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL))) THEN '0X' WHEN ("Project3".C1 = 1) AND ("Project3".C1 IS NOT NULL) THEN '0X0X' WHEN ("Project2".C1 = 1) AND ("Project2".C1 IS NOT NULL) THEN '0X1X' ELSE '0X2X' END LIKE '0X2X%')) AND ("Extent1".JOB_ID = :p__linq__0)

//:p__linq__0 is '399384'

This query only results a list of splices (38). There is no cables and no terminals. The query of elements where the job id is 399384 has 38 splices, 13 terminals and 39 cables.

What did I miss?


Thanks,
Nick

[Shalex]

We cannot reproduce the problem at the moment. We have sent a test project to the e-mail address specified in your forum profile. Looking forward to your reply.