Incorrect LEFT OUTER JOIN generated for DefaultIfEmpty

[object]

Hello,

I have a table CHANNELS that can be self-joined (some channels have parent channels), so the following statement retrieves joined information on both channels and its parents:

Code: Select all

SELECT *
FROM  CHANNELS c1
LEFT OUTER JOIN CHANNELS c2 ON c1.PARENT_CHANNEL_ID = c2.CHANNEL_ID

I am using the following LINQ statement in C#:

Code: Select all

    var query =
        from c in ctx.Channels
        join cp in ctx.Channels.DefaultIfEmpty() on c.ParentChannelId equals cp.ChannelId
        select new {c,cp};

The statement generated by DotConnect looks like this:

Code: Select all

SELECT *
FROM  CHANNELS "Extent1"
INNER JOIN  (SELECT 
	"SingleRowTable1".X,
	"Extent2".CHANNEL_ID,
	"Extent2".CHANNEL_NAME,
	"Extent2".PARENT_CHANNEL_ID
	FROM   ( SELECT 1 AS X FROM DUAL) "SingleRowTable1"
	LEFT OUTER JOIN CHANNELS "Extent2" ON 1 = 1 ) "Join1" ON ("Extent1".PARENT_CHANNEL_ID = "Join1".CHANNEL_ID) OR (("Extent1".PARENT_CHANNEL_ID IS NULL) AND ("Join1".CHANNEL_ID IS NULL))

... and when executed only works like INNER JOIN, i.e. no rows with NULL PARENT_CHANNEL_ID are included in the result which of course incorrect.

We are using Oracle 11 and the DotConnect version is 9.6.570.

Is this a know issue or is there other way to express LEFT OUTER JOIN?

[object]

Found the problem. LINQ requires different syntax for LEFT OUTER JOIN:

var query =
from c in ctx.Channels
join cp in ctx.Channels on c.ParentChannelId equals cp.ChannelId into _cp from cp in _cp.DefaultIfEmpty()
select new {c,cp};

This one worked flawlessly.