This question is locked. New answers and comments are not allowed.
Using the Northwind schema the following query (just for sake of simplicity I reuse the same tables in the query, my production query is much more complex):
generates the following SQL:
however this fails because the select list contains an NTEXT column which cannot be used with DISTINCT (MSSQL: error 421, Oracle: ORA-00932). I'd expect the following query to be generated:
A possible workaround is to replace Any() with Count()>0 however this still generates a semantically invalid query if q1 and q2 are left-joined. I've already described this latter issue in http://www.telerik.com/forums/wrong-sql-generated-with-joined-subqueries
using
( var db =
new
NorthwindEntities() )
{
var q1 = db.Categories.Where(p => db.Regions.Any(r => r.RegionID == p.CategoryID));
var q2 = db.Categories.Where(p => db.Regions.Any(r => r.RegionID == p.CategoryID));
var q = from p1
in
q1
join p2
in
q2 on p1.CategoryID equals p2.CategoryID
select
new
{ Desc1 = p1.Description, Desc2 = p2.Description };
var res = q.ToList();
}
SELECT
DISTINCT
a.[Description]
AS
COL1, b.[Description]
AS
COL2, c.[RegionID], d.[RegionID]
FROM
[Categories] a
JOIN
[Categories]
AS
b
ON
(a.[CategoryID] = b.[CategoryID])
CROSS
JOIN
[Region]
AS
c
CROSS
JOIN
[Region]
AS
d
WHERE
c.[RegionID] = a.[CategoryID]
AND
d.[RegionID] = b.[CategoryID]
SELECT
a.Description, b.Description
FROM
(
SELECT
*
FROM
Categories c
WHERE
EXISTS (
SELECT
1
FROM
Region r
WHERE
r.RegionID = c.CategoryID)
) a
INNER
JOIN
(
SELECT
*
FROM
Categories c
WHERE
EXISTS (
SELECT
1
FROM
Region r
WHERE
r.RegionID = c.CategoryID)
) b
ON
a.CategoryID=b.CategoryID