LINQ producing incorrect SQL

Question

As recommended by entityframework.codeplex.com, I am posting this issue with the tag entity-framework.

Database: PostgreSQL 9.1 Drivers: Npgsql 2.0.12

LINQ Query:

var q1 = customerComplaints
    .Where(x => x.ParentId == null)
    .Except(customerComplaints
                .Where(x => customerComplaints
                                .Any(c => (c.ParentId == x.Id))));

var q2 =
    (from a in customerComplaints
     join b in
         (customerComplaints.Where(a => a.ParentId != null)
                            .GroupBy(a => a.ParentId)
                            .Select(b => new { ParentId = b.Key, CreatedDate = b.Max(t => t.CreatedDate) }))
     on a.ParentId equals b.ParentId
     where a.CreatedDate == b.CreatedDate
     select a);

q1 = q1.Union(q2);

// Apply Sorting

if (string.IsNullOrEmpty(sorting) || sorting.Equals("CustomerName ASC")) {
    q1 = q1.OrderBy(p => p.Customer.Name);
} else if (sorting.Equals("CustomerName DESC")) {
    q1 = q1.OrderByDescending(p => p.Customer.Name);
} else if (sorting.Equals("ManagerName ASC")) {
    q1 = q1.OrderBy(p => p.Manager.LastName);
} else if (sorting.Equals("ManagerName DESC")) {
    q1 = q1.OrderByDescending(p => p.Manager.LastName);
} else if (sorting.Equals("AssignedName ASC")) {
    q1 = q1.OrderBy(p => p.Assigned.LastName);
} else if (sorting.Equals("AssignedName DESC")) {
    q1 = q1.OrderByDescending(p => p.Assigned.LastName);
} else if (sorting.Equals("StatusName ASC")) {
    q1 = q1.OrderBy(p => p.StatusId);
} else if (sorting.Equals("StatusName DESC")) {
    q1 = q1.OrderByDescending(p => p.StatusId);
} else if (sorting.Equals("ComplaintType ASC")) {
    q1 = q1.OrderBy(p => p.ComplaintTypeId);
} else if (sorting.Equals("ComplaintType DESC")) {
    q1 = q1.OrderByDescending(p => p.ComplaintTypeId);
} else if (sorting.Equals("CreatedDate ASC")) {
    q1 = q1.OrderBy(p => p.CreatedDate);
} else if (sorting.Equals("CreatedDate DESC")) {
    q1 = q1.OrderByDescending(p => p.CreatedDate);
}

// Perform paging and execute query
var result = q1.Skip(startIndex).Take(pageSize).ToList();

Resulting SQL

SELECT 
"Limit1"."C1" AS "C1"
"Limit1"."C2" AS "C2"           
"Limit1"."C3" AS "C3"   
"Limit1"."C4" AS "C4"           
"Limit1"."C5" AS "C5"           
"Limit1"."C6" AS "C6"               
"Limit1"."C7" AS "C7"               
"Limit1"."C8" AS "C8"               
"Limit1"."C9" AS "C9"               
"Limit1"."C10" AS "C10"         
"Limit1"."C11" AS "C11"             
"Limit1"."C12" AS "C12"         
"Limit1"."C13" AS "C13"         
"Limit1"."C14" AS "C14"         
"Limit1"."C15" AS "C15"         
"Limit1"."C16" AS "C16"         
"Limit1"."C17" AS "C17"         
"Limit1"."C18" AS "C18"         
"Limit1"."C19" AS "C19"             
"Limit1"."C20" AS "C20"         
"Limit1"."C21" AS "C21"         
"Limit1"."C22" AS "C22" 
FROM 
(SELECT DISTINCT 
    "UnionAll1"."C1" AS "C1"            
    "UnionAll1"."C2" AS "C2"
    "UnionAll1"."C3" AS "C3"                
    "UnionAll1"."C4" AS "C4"                
    "UnionAll1"."C5" AS "C5"                
    "UnionAll1"."C6" AS "C6"                
    "UnionAll1"."C7" AS "C7"                
    "UnionAll1"."C8" AS "C8"                
    "UnionAll1"."C9" AS "C9"                
    "UnionAll1"."C10" AS "C10"              
    "UnionAll1"."C11" AS "C11"              
    "UnionAll1"."C12" AS "C12"              
    "UnionAll1"."C13" AS "C13"              
    "UnionAll1"."C14" AS "C14"              
    "UnionAll1"."C15" AS "C15"              
    "UnionAll1"."C16" AS "C16"              
    "UnionAll1"."C17" AS "C17"              
    "UnionAll1"."C18" AS "C18"              
    "UnionAll1"."C19" AS "C19"              
    "UnionAll1"."C20" AS "C20"              
    "UnionAll1"."C21" AS "C21"              
    "UnionAll1"."C22" AS "C22" 
FROM 
    (SELECT 
        "Except1"."Id" AS "C1"              
        "Except1"."ManagerId" AS "C2"               
        "Except1"."AssignedId" AS "C3"              
        "Except1"."ComplaintTypeId" AS "C4"             
        "Except1"."RootCauseId" AS "C5"             
        "Except1"."StatusId" AS "C6"                
        "Except1"."ResultingFromId" AS "C7"             
        "Except1"."RespParty" AS "C8"               
        "Except1"."Condition" AS "C9"               
        "Except1"."Containment" AS "C10"                
        "Except1"."CorrectiveAction" AS "C11"               
        "Except1"."ProductRejected" AS "C12"                
        "Except1"."IssueDate" AS "C13"              
        "Except1"."DueDate" AS "C14"                
        "Except1"."Approved" AS "C15"               
        "Except1"."ApprovedById" AS "C16"               
        "Except1"."ApprovedDate" AS "C17"               
        "Except1"."RejectionDetail" AS "C18"                
        "Except1"."Parent" AS "C19"             
        "Except1"."ParentId" AS "C20"               
        "Except1"."CreatedDate" AS "C21"                
        "Except1"."CreatedId" AS "C22" 
    FROM 
        (SELECT 
            "Extent1"."Id" AS "Id",
            "Extent1"."ManagerId" AS "ManagerId",
            "Extent1"."AssignedId" AS "AssignedId",
            "Extent1"."ComplaintTypeId" AS "ComplaintTypeId",
            "Extent1"."RootCauseId" AS "RootCauseId",
            "Extent1"."StatusId" AS "StatusId",
            "Extent1"."ResultingFromId" AS "ResultingFromId",
            "Extent1"."RespParty" AS "RespParty",
            "Extent1"."Condition" AS "Condition",
            "Extent1"."Containment" AS "Containment",
            "Extent1"."CorrectiveAction" AS "CorrectiveAction",
            "Extent1"."ProductRejected" AS "ProductRejected",
            "Extent1"."IssueDate" AS "IssueDate",
            "Extent1"."DueDate" AS "DueDate",
            "Extent1"."Approved" AS "Approved",
            "Extent1"."ApprovedById" AS "ApprovedById",
            "Extent1"."ApprovedDate" AS "ApprovedDate",
            "Extent1"."RejectionDetail" AS "RejectionDetail",
            "Extent1"."Parent" AS "Parent",
            "Extent1"."ParentId" AS "ParentId",
            "Extent1"."CreatedDate" AS "CreatedDate",
            "Extent1"."CreatedId" AS "CreatedId" 
        FROM 
            "dbo"."CorrectiveActionRequest" AS "Extent1" 
        WHERE 
            "Extent1"."ParentId" IS NULL  
        EXCEPT 
            SELECT 
                "Extent2"."Id" AS "Id",
                "Extent2"."ManagerId" AS "ManagerId",
                "Extent2"."AssignedId" AS "AssignedId",
                "Extent2"."ComplaintTypeId" AS "ComplaintTypeId",
                "Extent2"."RootCauseId" AS "RootCauseId",
                "Extent2"."StatusId" AS "StatusId",
                "Extent2"."ResultingFromId" AS "ResultingFromId",
                "Extent2"."RespParty" AS "RespParty",
                "Extent2"."Condition" AS "Condition",
                "Extent2"."Containment" AS "Containment",
                "Extent2"."CorrectiveAction" AS "CorrectiveAction",
                "Extent2"."ProductRejected" AS "ProductRejected",
                "Extent2"."IssueDate" AS "IssueDate",
                "Extent2"."DueDate" AS "DueDate",
                "Extent2"."Approved" AS "Approved",
                "Extent2"."ApprovedById" AS "ApprovedById",
                "Extent2"."ApprovedDate" AS "ApprovedDate",
                "Extent2"."RejectionDetail" AS "RejectionDetail",
                "Extent2"."Parent" AS "Parent",
                "Extent2"."ParentId" AS "ParentId",
                "Extent2"."CreatedDate" AS "CreatedDate",
                "Extent2"."CreatedId" AS "CreatedId" 
            FROM 
                "dbo"."CorrectiveActionRequest" AS "Extent2" 
            WHERE EXISTS 
                (SELECT 
                    1 AS "C1" 
                FROM 
                    "dbo"."CorrectiveActionRequest" AS "Extent3" 
                WHERE 
                    "Extent3"."ParentId"="Extent2"."Id")) AS "Except1" 
                UNION ALL 
                    SELECT 
                        "Extent4"."Id" AS "Id",
                        "Extent4"."ManagerId" AS "ManagerId",
                        "Extent4"."AssignedId" AS "AssignedId",
                        "Extent4"."ComplaintTypeId" AS "ComplaintTypeId",
                        "Extent4"."RootCauseId" AS "RootCauseId",
                        "Extent4"."StatusId" AS "StatusId",
                        "Extent4"."ResultingFromId" AS "ResultingFromId",
                        "Extent4"."RespParty" AS "RespParty",
                        "Extent4"."Condition" AS "Condition",
                        "Extent4"."Containment" AS "Containment",
                        "Extent4"."CorrectiveAction" AS "CorrectiveAction",
                        "Extent4"."ProductRejected" AS "ProductRejected",
                        "Extent4"."IssueDate" AS "IssueDate",
                        "Extent4"."DueDate" AS "DueDate",
                        "Extent4"."Approved" AS "Approved",
                        "Extent4"."ApprovedById" AS "ApprovedById",
                        "Extent4"."ApprovedDate" AS "ApprovedDate",
                        "Extent4"."RejectionDetail" AS "RejectionDetail",
                        "Extent4"."Parent" AS "Parent",
                        "Extent4"."ParentId" AS "ParentId",
                        "Extent4"."CreatedDate" AS "CreatedDate",
                        "Extent4"."CreatedId" AS "CreatedId" 
                    FROM 
                        "dbo"."CorrectiveActionRequest" AS "Extent4" 
                    INNER JOIN 
                        (SELECT 
                            "Extent5"."ParentId" AS "K1",
                        CAST 
                            (max("Extent5"."CreatedDate") AS timestamp) AS "A1" 
                        FROM 
                            "dbo"."CorrectiveActionRequest" AS "Extent5" 
                        WHERE
                            "Extent5"."ParentId" IS NOT NULL  
                        GROUP BY 
                            "Extent5"."ParentId") AS "GroupBy1" 
                        ON 
                            (("Extent4"."ParentId"="GroupBy1"."K1") 
                        OR 
                            (("Extent4"."ParentId" IS NULL ) 
                        AND 
                            ("GroupBy1"."K1" IS NULL ))) 
                        AND 
                            ("Extent4"."CreatedDate"="GroupBy1"."A1")) AS "UnionAll1") AS "Limit1" 
                        ORDER BY "Distinct1"."C7" ASC  
                        OFFSET 0 
                        LIMIT 10

The culprit is at the very end:

("Extent4"."CreatedDate"="GroupBy1"."A1")) AS "UnionAll1") AS "Limit1" 
                        ORDER BY "Distinct1"."C7" ASC  
                        OFFSET 0 
                        LIMIT 10

It attempts to order by Distinct1 yet no such identifier exists anywhere else in the generated SQL query. I have been attempting to debug this for several days and noticed that it is dependent on what I choose to sort by. For example, sorting by Customer.Name will produce a working query. Sorting by CreatedDate will not.

Answer 1

It probably meant LIMIT1. Looks like you are using MySQL and the MySQL driver has a bug. I would check to make sure that your driver is up to date, or file a bug with them.