[Solved] Invalid query with NTEXT columns

[Greg]

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):

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();
}

generates the following SQL:

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]

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:

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

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

[Thomas]

Hi Greg,

thanks for reporting this issue with us. There is unfortunately no easy fix, just a very specific work-around to prevent this issue appearing in the application:

 var q1 = db.Categoris.Where(p => db.Regions.Any(r => r.Id == p.Id) || (1==0));
 var q2 = db.Categoris.Where(p => db.Regions.Any(r => r.Id == p.Id) || (1==0));
 var q = from p1 in q1
              join p2 in q2 on p1.Id equals p2.Id
              select new { Desc1 = p1.Description, Desc2 = p2.Description };
 var res = q.ToList();

By appending the useless (1==0) condition the internal representation is shifted to an OR node and that's why the runtime does not append the distinct any longer. 

I will file a bug report for this; thanks for reporting.

Regards,
Thomas
Telerik