[Solved] Wrong ordering of join clauses

[Greg]

Using NorthwindEF. For sake of brevity I'm using a meaningless query for reproducing the issue:

var db = new NorthwindModel();
var q1 = db.Products.AsQueryable();
var q2 = db.Products.AsQueryable();
short quant = 1;
q1 = q1.Where(p =>
(
    from y in db.Regions
    where y.RegionID == p.ProductID
    join od in db.OrderDetails on new { Quantity = quant, Id = p.ProductID, } equals new { od.Quantity, Id = od.OrderID, } into odg
    from od in odg.DefaultIfEmpty()
    where od.Discount > 0
    select 1
).Any());
q2 = q2.Where(p =>
(
    from y in db.Regions
    where y.RegionID == p.ProductID
    join od in db.OrderDetails on new { Quantity = quant, Id = p.ProductID, } equals new { od.Quantity, Id = od.OrderID, } into odg
    from od in odg.DefaultIfEmpty()
    where od.Discount > 0
    select 1
).Any());

var q = from d1 in q1
        join d2 in q2 on d1.ProductID equals d2.ProductID
        select d1.ProductName + d2.ProductName;

Side note: while q1 and q2 are essentially the same OA fails to generate the sql command if I use the same instance.
The generated SQL is:

SELECT DISTINCT a.[ProductName] AS COL1, d.[ProductName] AS COL2, 1, 1
FROM [Products] a
LEFT JOIN [Order Details] AS b ON (? = b.[Quantity] AND a.[ProductID] = b.[OrderID])
LEFT JOIN [Order Details] AS c ON (? = c.[Quantity] AND d.[ProductID] = c.[OrderID])
JOIN [Products] AS d ON (a.[ProductID] = d.[ProductID])
CROSS JOIN [Region] AS e
CROSS JOIN [Region] AS f
WHERE e.[RegionID] = a.[ProductID]
AND b.[Discount] > 0
AND f.[RegionID] = d.[ProductID]
AND c.[Discount] > 0

The OrderDetails extents in the subquery are moved to the outer FROM clause but not in the right order: line 4 references alias 'd' declared in line 5. Ideally I'd expect the following SQL:

SELECT DISTINCT a.[ProductName] AS COL1, d.[ProductName] AS COL2, 1, 1
FROM [Products] a
JOIN [Products] AS d ON (a.[ProductID] = d.[ProductID])
WHERE EXISTS
(
    SELECT 1
    FROM [Region] e
    LEFT JOIN [Order Details] AS b ON (? = b.[Quantity] AND a.[ProductID] = b.[ProductID])
    WHERE e.[RegionID] = a.[ProductID]
          AND b.[Discount] > 0
)
AND EXISTS
(
    SELECT 1
    FROM [Region] f
    LEFT JOIN [Order Details] AS c ON (? = c.[Quantity] AND d.[ProductID] = c.[ProductID])
    WHERE f.[RegionID] = d.[ProductID]
          AND c.[Discount] > 0
)

with extents NOT moved outside to the top level join clause (also see my post here why this fails in some cases: http://www.telerik.com/forums/linq-subquery-errors), or if they must, then at least they should be in the proper order, like this:

SELECT DISTINCT a.[ProductName] AS COL1, d.[ProductName] AS COL2, 1, 1
FROM [Products] a
LEFT JOIN [Order Details] AS b ON (? = b.[Quantity] AND a.[ProductID] = b.[OrderID])
JOIN [Products] AS d ON (a.[ProductID] = d.[ProductID])
LEFT JOIN [Order Details] AS c ON (? = c.[Quantity] AND d.[ProductID] = c.[OrderID])
CROSS JOIN [Region] AS e
CROSS JOIN [Region] AS f
WHERE e.[RegionID] = a.[ProductID]
AND b.[Discount] > 0
AND f.[RegionID] = d.[ProductID]
AND c.[Discount] > 0

[Thomas]

Hi Greg,

with the correction made for the other forum post by you the queries will now processed correctly and produce the CROSS JOIN form. I've verified this and added a respective test.

Regards,
Thomas
Telerik