This question is locked. New answers and comments are not allowed.
Using NorthwindEF. For sake of brevity I'm using a meaningless query for reproducing the issue:
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:
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:
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:
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