Gary O. Stenstrom Gary O. Stenstrom - 24 days ago 7
C# Question

SQL Generated From LINQ Query against Telerik ORM

I am experiencing some confusion on the SQL generated from a fairly standard LINQ query using a LEFT OUTER JOIN. The query is against the Free Telerik Open Access/Data Access product.

I wrote the following LINQ ...

01.var sessionQ = from sessionHdr in headersQ
02. join sessionBody in bodyQ on
03. sessionHdr.ID equals sessionBody.ID into sessionList
04. from sessionJSON in sessionList.DefaultIfEmpty()
05. where sessionHdr.Format == 13
06.
07. select new WITransmitsVP
08. {
09. ...
10. };


Using this LINQ syntax for an OUTER JOIN I was expecting the SQL (ORACLE) generated to look something like the following ...

1.SELECT
2. ...
3.
4.FROM "XFEVP"."WI_TRANSMITS_VP" a
5. INNER JOIN "XFEVP"."WI_TRANSMITS_VP_MSG" b ON
6. a."MESSAGE_ID" = b."MESSAGE_ID"
7.WHERE a."MESSAGE_ID" = 60412762;


But instead when I log the output I am seeing ...

01.SELECT
02. ...
03.
04.FROM "XFEVP"."WI_TRANSMITS_VP" a
05.WHERE a."MESSAGE_ID" = :p0
06.ORDER BY COL1
07.
08.SELECT
09. ...
10.
11.FROM "XFEVP"."WI_TRANSMITS_VP_MSG" a
12.ORDER BY COL1


Instead of a single SELECT with OUTER JOIN I am seeing two entirely separate SELECT statements. Since the each table contains MILLIONS of records and presumably ALL the contents of the second table are being queried rather than just those joined to the results of the first SELECT statement the component making the call eventually crashes due to memory issues after many many minutes of latency.

Why am I not getting a actual JOIN statement in the generated SQL? Is there a workaround a fix or am I simply doing something wrong?

SOLUTION

Thanks to EVK for providing the answer. It turns out that the problem was in the way I was defining the IQueryables. I swapped out the IQuaryables with references to the DBContext Collections instead ...

01.var sessionQ = from sessionHdr in DBContext.SessionHeaders
02. join sessionBody in DBContext.SessionBodies on
03. sessionHdr.ID equals sessionBody.ID into sessionList
04. from sessionJSON in sessionList.DefaultIfEmpty()
05. where sessionHdr.Format == 13
06.
07. select new WITransmitsVP
08. {
09. ...
10. };


This resulted in the following generated SQL ...

SELECT

...

FROM "XFEVP"."WI_TRANSMITS_VP" a
LEFT JOIN "XFEVP"."WI_TRANSMITS_VP_MSG" b ON
(a."MESSAGE_ID" = b."MESSAGE_ID")
WHERE a."FORMAT" = :p0

Evk Evk
Answer

My guess would be that headersQ and bodyQ represent some relatively complex query (maybe not for you, but for ORM you use), and so it has some troubles building single query which combines them. So instead of throwing some exception it does something bad - pulles both query results into memory and combines them there.

Comments