Bug #77088 Incorrect SQL generated when (complex) querying self-referencing table
Submitted: 19 May 2015 8:59 Modified: 20 May 2015 5:31
Reporter: Eskild D Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.6 OS:Windows
Assigned to: CPU Architecture:Any

[19 May 2015 8:59] Eskild D 
Description:
MySQL Server version: (x64) 5.6.24 (Community)
Connector/NET version: 6.9.6
EntityFramework version: 6.1.3
Microsoft.OData.Core version: 6.10.0
Microsoft.AspNet.OData version: 5.5.1

When executing below query results in an error: Unknown column 'Extent3.Id' in 'on clause'

    GET ~/odata/Trees?$expand=Children($levels=max;$expand=Others),Others

The problem is: ;$expand=Others
Which cause an incorrect query to be generated. This works when using MSSQL.

Sorry I couldn't figure out how to extract the LINQ expression OData executes.

Below is the incorrect query generated:

SELECT
`UnionAll2`.`Id` AS `C1`, 
`UnionAll2`.`C2`, 
`UnionAll2`.`Id1` AS `C3`, 
`UnionAll2`.`ParentId` AS `C4`, 
`UnionAll2`.`Id2` AS `C5`, 
`UnionAll2`.`C3` AS `C6`, 
`UnionAll2`.`Id3` AS `C7`, 
`UnionAll2`.`C4` AS `C8`, 
`UnionAll2`.`C1` AS `C9`, 
`UnionAll2`.`Id4` AS `C10`, 
`UnionAll2`.`C5` AS `C11`, 
`UnionAll2`.`Id5` AS `C12`, 
`UnionAll2`.`TreeId` AS `C13`, 
`UnionAll2`.`C6` AS `C14`, 
`UnionAll2`.`C7` AS `C15`, 
`UnionAll2`.`C8` AS `C16`, 
`UnionAll2`.`C9` AS `C17`, 
`UnionAll2`.`C10` AS `C18`, 
`UnionAll2`.`C11` AS `C19`, 
`UnionAll2`.`C12` AS `C20`, 
`UnionAll2`.`C13` AS `C21`, 
`UnionAll2`.`C14` AS `C22`, 
`UnionAll2`.`C15` AS `C23`, 
`UnionAll2`.`C16` AS `C24`, 
`UnionAll2`.`C17` AS `C25`, 
`UnionAll2`.`C18` AS `C26`, 
`UnionAll2`.`C19` AS `C27`, 
`UnionAll2`.`C20` AS `C28`, 
`UnionAll2`.`C21` AS `C29`, 
`UnionAll2`.`C22` AS `C30`, 
`UnionAll2`.`C23` AS `C31`, 
`UnionAll2`.`C24` AS `C32`, 
`UnionAll2`.`C25` AS `C33`, 
`UnionAll2`.`C26` AS `C34`, 
`UnionAll2`.`C27` AS `C35`, 
`UnionAll2`.`C28` AS `C36`, 
`UnionAll2`.`C29` AS `C37`
FROM ((SELECT
CASE WHEN (`Extent2`.`Id` IS  NULL) THEN (NULL)  ELSE (1) END AS `C1`, 
`Extent1`.`Id`, 
@gp1 AS `C2`, 
`Extent1`.`Id` AS `Id1`, 
`Extent1`.`ParentId`, 
`Extent1`.`Id` AS `Id2`, 
@gp2 AS `C3`, 
`Extent1`.`Id` AS `Id3`, 
@gp3 AS `C4`, 
`Extent2`.`Id` AS `Id4`, 
CASE WHEN (`Extent2`.`Id` IS  NULL) THEN (NULL)  ELSE (@gp4) END AS `C5`, 
`Extent2`.`Id` AS `Id5`, 
`Extent2`.`TreeId`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
NULL AS `C9`, 
NULL AS `C10`, 
NULL AS `C11`, 
NULL AS `C12`, 
NULL AS `C13`, 
NULL AS `C14`, 
NULL AS `C15`, 
NULL AS `C16`, 
NULL AS `C17`, 
NULL AS `C18`, 
NULL AS `C19`, 
NULL AS `C20`, 
NULL AS `C21`, 
NULL AS `C22`, 
NULL AS `C23`, 
NULL AS `C24`, 
NULL AS `C25`, 
NULL AS `C26`, 
NULL AS `C27`, 
NULL AS `C28`, 
NULL AS `C29`
FROM `Trees` AS `Extent1` LEFT OUTER JOIN `Other` AS `Extent2` ON `Extent1`.`Id` = `Extent2`.`TreeId`) UNION ALL (SELECT
2 AS `C1`, 
`Project2`.`Id`, 
`Project2`.`C1` AS `C2`, 
`Project2`.`Id` AS `Id1`, 
`Project2`.`ParentId`, 
`Project2`.`Id` AS `Id2`, 
`Project2`.`C2` AS `C3`, 
`Project2`.`Id` AS `Id3`, 
`Project2`.`C3` AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
`UnionAll1`.`Id` AS `C9`, 
`UnionAll1`.`C2` AS `C10`, 
`UnionAll1`.`Id1` AS `C11`, 
`UnionAll1`.`ParentId` AS `C12`, 
`UnionAll1`.`Id2` AS `C13`, 
`UnionAll1`.`C3` AS `C14`, 
`UnionAll1`.`Id3` AS `C15`, 
`UnionAll1`.`C4` AS `C16`, 
`UnionAll1`.`C1` AS `C17`, 
`UnionAll1`.`Id4` AS `C18`, 
`UnionAll1`.`C5` AS `C19`, 
`UnionAll1`.`Id5` AS `C20`, 
`UnionAll1`.`TreeId` AS `C21`, 
`UnionAll1`.`C6` AS `C22`, 
`UnionAll1`.`C7` AS `C23`, 
`UnionAll1`.`C8` AS `C24`, 
`UnionAll1`.`C9` AS `C25`, 
`UnionAll1`.`C10` AS `C26`, 
`UnionAll1`.`C11` AS `C27`, 
`UnionAll1`.`C12` AS `C28`, 
`UnionAll1`.`C13` AS `C29`, 
`UnionAll1`.`C14` AS `C30`, 
`UnionAll1`.`C15` AS `C31`, 
`UnionAll1`.`C16` AS `C32`
FROM (SELECT
`Extent3`.`Id`, 
`Extent3`.`ParentId`, 
@gp5 AS `C1`, 
@gp6 AS `C2`, 
@gp7 AS `C3`
FROM `Trees` AS `Extent3`) AS `Project2` LEFT OUTER JOIN ((SELECT
CASE WHEN (`Extent5`.`Id` IS  NULL) THEN (NULL)  ELSE (1) END AS `C1`, 
`Extent4`.`Id`, 
@gp8 AS `C2`, 
`Extent4`.`Id` AS `Id1`, 
`Extent4`.`ParentId`, 
`Extent4`.`Id` AS `Id2`, 
@gp9 AS `C3`, 
`Extent4`.`Id` AS `Id3`, 
@gp10 AS `C4`, 
`Extent5`.`Id` AS `Id4`, 
CASE WHEN (`Extent5`.`Id` IS  NULL) THEN (NULL)  ELSE (@gp11) END AS `C5`, 
`Extent5`.`Id` AS `Id5`, 
`Extent5`.`TreeId`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
NULL AS `C9`, 
NULL AS `C10`, 
NULL AS `C11`, 
NULL AS `C12`, 
NULL AS `C13`, 
NULL AS `C14`, 
NULL AS `C15`, 
NULL AS `C16`
FROM `Trees` AS `Extent4` LEFT OUTER JOIN `Other` AS `Extent5` ON `Extent4`.`Id` = `Extent5`.`TreeId`
 WHERE 1 = 1) UNION ALL (SELECT
2 AS `C1`, 
`Extent6`.`Id`, 
@gp12 AS `C2`, 
`Extent6`.`Id` AS `Id1`, 
`Extent6`.`ParentId`, 
`Extent6`.`Id` AS `Id2`, 
@gp13 AS `C3`, 
`Extent6`.`Id` AS `Id3`, 
@gp14 AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
`Join3`.`Id` AS `Id4`, 
@gp15 AS `C9`, 
`Join3`.`Id` AS `Id5`, 
`Join3`.`ParentId` AS `ParentId1`, 
`Join3`.`Id` AS `Id6`, 
@gp16 AS `C10`, 
CASE WHEN (`Join3`.`ID1` IS  NULL) THEN (NULL)  ELSE (1) END AS `C11`, 
`Join3`.`ID1` AS `Id7`, 
CASE WHEN (`Join3`.`ID1` IS  NULL) THEN (NULL)  ELSE (@gp17) END AS `C12`, 
`Join3`.`ID1` AS `Id8`, 
`Join3`.`TreeId`
FROM `Trees` AS `Extent6` INNER JOIN (SELECT
`Extent7`.`Id`, 
`Extent7`.`ParentId`, 
`Extent8`.`Id` AS `ID1`, 
`Extent8`.`TreeId`
FROM `Trees` AS `Extent7` LEFT OUTER JOIN `Other` AS `Extent8` ON `Extent7`.`Id` = `Extent8`.`TreeId`) AS `Join3` ON `Extent6`.`Id` = `Join3`.`ParentId`
 WHERE 1 = 1)) AS `UnionAll1` ON `Extent3`.`Id` = `UnionAll1`.`ParentId`)) AS `UnionAll2`
 ORDER BY 
`UnionAll2`.`Id1` ASC, 
`UnionAll2`.`C1` ASC, 
`UnionAll2`.`C8` ASC, 
`UnionAll2`.`C14` ASC, 
`UnionAll2`.`C21` ASC, 
`UnionAll2`.`C25` ASC

-- @gp1: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- @gp2: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp3: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp4: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- @gp5: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- @gp6: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp7: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp8: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- @gp9: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp10: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp11: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- @gp12: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- @gp13: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp14: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp15: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- @gp16: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp17: '49952a3b-fed3-432e-9f92-f605e89ded0e' (Type = String, IsNullable = false, Size = 36)

-- Executing at 19-05-2015 10:36:24 +02:00

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
-- Failed in 74 ms with error: Unknown column 'Extent3.Id' in 'on clause'

How to repeat:
Setup a ASP.NET OData Web Api endpoint: http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/create-an-oda...

Model:
public class Tree
{
	public int Id { get; set; }
	public int? ParentId { get; set; }
	public virtual Tree Parent { get; set; }
	public virtual ICollection<Tree> Children { get; set; }
	public virtual ICollection<Other> Others { get; set; }
}

public class Other
{
	public int Id { get; set; }
	public int? TreeId { get; set; }
	public virtual Tree Tree { get; set; }
}

DbContext:
[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class TestContext : DbContext
{
	public TestContext()
		: base("MySqlContext")
	{
	}

	public DbSet<Tree> Trees { get; set; }
}

Web config setup:
public static class WebApiConfig
{
	public static void Register(HttpConfiguration config)
	{
		config.MapODataServiceRoute(
			routeName: "odata",
			routePrefix: "odata",
			model: GetConventionModel());
	}

	private static IEdmModel GetConventionModel()
	{
		var builder = new ODataConventionModelBuilder();
		builder.EntitySet<Tree>("Trees");
		return builder.GetEdmModel();
	}
}

OData Controller:
public class TreesController : ODataController
{
	private TestContext db = new TestContext();

	public TreesController()
	{
		db.Database.Log = message => Debug.WriteLine(message);
	}

	// GET: odata/Trees
	[EnableQuery]
	public IQueryable<Tree> Get()
	{
		return db.Trees;
	}

	protected override void Dispose(bool disposing)
	{
		if (disposing)
		{
			db.Dispose();
		}
		base.Dispose(disposing);
	}
}

Run it and go to this url: http://localhost:<port>/odata/Trees?$expand=Children($levels=max;$expand=Others),Others

And watch it fail. Inspect the SQL (included in the description) in the diagnostics window (visual studio).

Suggested fix:
IF I change the connection string to a use an SqlServer (MSSQL) then it works. 

Here's the SQL generated for SqlServer: (below query is valid)

SELECT 
    [UnionAll2].[Id] AS [C1], 
    [UnionAll2].[C2] AS [C2], 
    [UnionAll2].[Id1] AS [C3], 
    [UnionAll2].[ParentId] AS [C4], 
    [UnionAll2].[Id2] AS [C5], 
    [UnionAll2].[C3] AS [C6], 
    [UnionAll2].[Id3] AS [C7], 
    [UnionAll2].[C4] AS [C8], 
    [UnionAll2].[C1] AS [C9], 
    [UnionAll2].[Id4] AS [C10], 
    [UnionAll2].[C5] AS [C11], 
    [UnionAll2].[Id5] AS [C12], 
    [UnionAll2].[TreeId] AS [C13], 
    [UnionAll2].[C6] AS [C14], 
    [UnionAll2].[C7] AS [C15], 
    [UnionAll2].[C8] AS [C16], 
    [UnionAll2].[C9] AS [C17], 
    [UnionAll2].[C10] AS [C18], 
    [UnionAll2].[C11] AS [C19], 
    [UnionAll2].[C12] AS [C20], 
    [UnionAll2].[C13] AS [C21], 
    [UnionAll2].[C14] AS [C22], 
    [UnionAll2].[C15] AS [C23], 
    [UnionAll2].[C16] AS [C24], 
    [UnionAll2].[C17] AS [C25], 
    [UnionAll2].[C18] AS [C26], 
    [UnionAll2].[C19] AS [C27], 
    [UnionAll2].[C20] AS [C28], 
    [UnionAll2].[C21] AS [C29], 
    [UnionAll2].[C22] AS [C30], 
    [UnionAll2].[C23] AS [C31], 
    [UnionAll2].[C24] AS [C32], 
    [UnionAll2].[C25] AS [C33], 
    [UnionAll2].[C26] AS [C34], 
    [UnionAll2].[C27] AS [C35], 
    [UnionAll2].[C28] AS [C36], 
    [UnionAll2].[C29] AS [C37]
    FROM  (SELECT 
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        [Extent1].[Id] AS [Id], 
        N'0acdad70-1aaf-4dd5-9896-5af594ba8175' AS [C2], 
        [Extent1].[Id] AS [Id1], 
        [Extent1].[ParentId] AS [ParentId], 
        [Extent1].[Id] AS [Id2], 
        N'Others' AS [C3], 
        [Extent1].[Id] AS [Id3], 
        N'Children' AS [C4], 
        [Extent2].[Id] AS [Id4], 
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE N'0acdad70-1aaf-4dd5-9896-5af594ba8175' END AS [C5], 
        [Extent2].[Id] AS [Id5], 
        [Extent2].[TreeId] AS [TreeId], 
        CAST(NULL AS int) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS int) AS [C8], 
        CAST(NULL AS int) AS [C9], 
        CAST(NULL AS int) AS [C10], 
        CAST(NULL AS varchar(1)) AS [C11], 
        CAST(NULL AS int) AS [C12], 
        CAST(NULL AS varchar(1)) AS [C13], 
        CAST(NULL AS int) AS [C14], 
        CAST(NULL AS int) AS [C15], 
        CAST(NULL AS varchar(1)) AS [C16], 
        CAST(NULL AS int) AS [C17], 
        CAST(NULL AS int) AS [C18], 
        CAST(NULL AS int) AS [C19], 
        CAST(NULL AS varchar(1)) AS [C20], 
        CAST(NULL AS int) AS [C21], 
        CAST(NULL AS int) AS [C22], 
        CAST(NULL AS int) AS [C23], 
        CAST(NULL AS varchar(1)) AS [C24], 
        CAST(NULL AS int) AS [C25], 
        CAST(NULL AS int) AS [C26], 
        CAST(NULL AS varchar(1)) AS [C27], 
        CAST(NULL AS int) AS [C28], 
        CAST(NULL AS int) AS [C29]
        FROM  [dbo].[Trees] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Other] AS [Extent2] ON [Extent1].[Id] = [Extent2].[TreeId]
    UNION ALL
        SELECT 
        2 AS [C1], 
        [Project2].[Id] AS [Id], 
        [Project2].[C1] AS [C2], 
        [Project2].[Id] AS [Id1], 
        [Project2].[ParentId] AS [ParentId], 
        [Project2].[Id] AS [Id2], 
        [Project2].[C2] AS [C3], 
        [Project2].[Id] AS [Id3], 
        [Project2].[C3] AS [C4], 
        CAST(NULL AS int) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS int) AS [C7], 
        CAST(NULL AS int) AS [C8], 
        [UnionAll1].[Id] AS [C9], 
        [UnionAll1].[C2] AS [C10], 
        [UnionAll1].[Id1] AS [C11], 
        [UnionAll1].[ParentId] AS [C12], 
        [UnionAll1].[Id2] AS [C13], 
        [UnionAll1].[C3] AS [C14], 
        [UnionAll1].[Id3] AS [C15], 
        [UnionAll1].[C4] AS [C16], 
        [UnionAll1].[C1] AS [C17], 
        [UnionAll1].[Id4] AS [C18], 
        [UnionAll1].[C5] AS [C19], 
        [UnionAll1].[Id5] AS [C20], 
        [UnionAll1].[TreeId] AS [C21], 
        [UnionAll1].[C6] AS [C22], 
        [UnionAll1].[C7] AS [C23], 
        [UnionAll1].[C8] AS [C24], 
        [UnionAll1].[C9] AS [C25], 
        [UnionAll1].[C10] AS [C26], 
        [UnionAll1].[C11] AS [C27], 
        [UnionAll1].[C12] AS [C28], 
        [UnionAll1].[C13] AS [C29], 
        [UnionAll1].[C14] AS [C30], 
        [UnionAll1].[C15] AS [C31], 
        [UnionAll1].[C16] AS [C32]
        FROM   (SELECT 
            [Extent3].[Id] AS [Id], 
            [Extent3].[ParentId] AS [ParentId], 
            N'0acdad70-1aaf-4dd5-9896-5af594ba8175' AS [C1], 
            N'Others' AS [C2], 
            N'Children' AS [C3]
            FROM [dbo].[Trees] AS [Extent3] ) AS [Project2]
        CROSS APPLY  (SELECT 
            CASE WHEN ([Extent5].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
            [Extent4].[Id] AS [Id], 
            N'0acdad70-1aaf-4dd5-9896-5af594ba8175' AS [C2], 
            [Extent4].[Id] AS [Id1], 
            [Extent4].[ParentId] AS [ParentId], 
            [Extent4].[Id] AS [Id2], 
            N'Others' AS [C3], 
            [Extent4].[Id] AS [Id3], 
            N'Children' AS [C4], 
            [Extent5].[Id] AS [Id4], 
            CASE WHEN ([Extent5].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE N'0acdad70-1aaf-4dd5-9896-5af594ba8175' END AS [C5], 
            [Extent5].[Id] AS [Id5], 
            [Extent5].[TreeId] AS [TreeId], 
            CAST(NULL AS int) AS [C6], 
            CAST(NULL AS varchar(1)) AS [C7], 
            CAST(NULL AS int) AS [C8], 
            CAST(NULL AS int) AS [C9], 
            CAST(NULL AS int) AS [C10], 
            CAST(NULL AS varchar(1)) AS [C11], 
            CAST(NULL AS int) AS [C12], 
            CAST(NULL AS int) AS [C13], 
            CAST(NULL AS varchar(1)) AS [C14], 
            CAST(NULL AS int) AS [C15], 
            CAST(NULL AS int) AS [C16]
            FROM  [dbo].[Trees] AS [Extent4]
            LEFT OUTER JOIN [dbo].[Other] AS [Extent5] ON [Extent4].[Id] = [Extent5].[TreeId]
            WHERE [Project2].[Id] = [Extent4].[ParentId]
        UNION ALL
            SELECT 
            2 AS [C1], 
            [Extent6].[Id] AS [Id], 
            N'0acdad70-1aaf-4dd5-9896-5af594ba8175' AS [C2], 
            [Extent6].[Id] AS [Id1], 
            [Extent6].[ParentId] AS [ParentId], 
            [Extent6].[Id] AS [Id2], 
            N'Others' AS [C3], 
            [Extent6].[Id] AS [Id3], 
            N'Children' AS [C4], 
            CAST(NULL AS int) AS [C5], 
            CAST(NULL AS varchar(1)) AS [C6], 
            CAST(NULL AS int) AS [C7], 
            CAST(NULL AS int) AS [C8], 
            [Join3].[Id1] AS [Id4], 
            N'0acdad70-1aaf-4dd5-9896-5af594ba8175' AS [C9], 
            [Join3].[Id1] AS [Id5], 
            [Join3].[ParentId] AS [ParentId1], 
            [Join3].[Id1] AS [Id6], 
            N'Others' AS [C10], 
            CASE WHEN ([Join3].[Id2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C11], 
            [Join3].[Id2] AS [Id7], 
            CASE WHEN ([Join3].[Id2] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE N'0acdad70-1aaf-4dd5-9896-5af594ba8175' END AS [C12], 
            [Join3].[Id2] AS [Id8], 
            [Join3].[TreeId] AS [TreeId]
            FROM  [dbo].[Trees] AS [Extent6]
            INNER JOIN  (SELECT [Extent7].[Id] AS [Id1], [Extent7].[ParentId] AS [ParentId], [Extent8].[Id] AS [Id2], [Extent8].[TreeId] AS [TreeId]
                FROM  [dbo].[Trees] AS [Extent7]
                LEFT OUTER JOIN [dbo].[Other] AS [Extent8] ON [Extent7].[Id] = [Extent8].[TreeId] ) AS [Join3] ON [Extent6].[Id] = [Join3].[ParentId]
            WHERE [Project2].[Id] = [Extent6].[ParentId]) AS [UnionAll1]) AS [UnionAll2]
    ORDER BY [UnionAll2].[Id1] ASC, [UnionAll2].[C1] ASC, [UnionAll2].[C8] ASC, [UnionAll2].[C14] ASC, [UnionAll2].[C21] ASC, [UnionAll2].[C25] ASC
[19 May 2015 9:11] Eskild D 
Solution ready to go

Attachment: ODataQueryFail.rar (application/octet-stream, text), 13.52 KiB.
[20 May 2015 5:31] Chiranjeevi Battula 
Hello Eskild D,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.6 and EF 6.1.3.

Thanks,
Chiranjeevi.
[20 May 2015 5:33] Chiranjeevi Battula 
generated sql query:

SELECT
`UnionAll2`.`Id` AS `C1`, 
`UnionAll2`.`C2`, 
`UnionAll2`.`Id1` AS `C3`, 
`UnionAll2`.`ParentId` AS `C4`, 
`UnionAll2`.`Id2` AS `C5`, 
`UnionAll2`.`C3` AS `C6`, 
`UnionAll2`.`Id3` AS `C7`, 
`UnionAll2`.`C4` AS `C8`, 
`UnionAll2`.`C1` AS `C9`, 
`UnionAll2`.`Id4` AS `C10`, 
`UnionAll2`.`C5` AS `C11`, 
`UnionAll2`.`Id5` AS `C12`, 
`UnionAll2`.`TreeId` AS `C13`, 
`UnionAll2`.`C6` AS `C14`, 
`UnionAll2`.`C7` AS `C15`, 
`UnionAll2`.`C8` AS `C16`, 
`UnionAll2`.`C9` AS `C17`, 
`UnionAll2`.`C10` AS `C18`, 
`UnionAll2`.`C11` AS `C19`, 
`UnionAll2`.`C12` AS `C20`, 
`UnionAll2`.`C13` AS `C21`, 
`UnionAll2`.`C14` AS `C22`, 
`UnionAll2`.`C15` AS `C23`, 
`UnionAll2`.`C16` AS `C24`, 
`UnionAll2`.`C17` AS `C25`, 
`UnionAll2`.`C18` AS `C26`, 
`UnionAll2`.`C19` AS `C27`, 
`UnionAll2`.`C20` AS `C28`, 
`UnionAll2`.`C21` AS `C29`, 
`UnionAll2`.`C22` AS `C30`, 
`UnionAll2`.`C23` AS `C31`, 
`UnionAll2`.`C24` AS `C32`, 
`UnionAll2`.`C25` AS `C33`, 
`UnionAll2`.`C26` AS `C34`, 
`UnionAll2`.`C27` AS `C35`, 
`UnionAll2`.`C28` AS `C36`, 
`UnionAll2`.`C29` AS `C37`
FROM ((SELECT
CASE WHEN (`Extent2`.`Id` IS  NULL) THEN (NULL)  ELSE (1) END AS `C1`, 
`Extent1`.`Id`, 
@gp1 AS `C2`, 
`Extent1`.`Id` AS `Id1`, 
`Extent1`.`ParentId`, 
`Extent1`.`Id` AS `Id2`, 
@gp2 AS `C3`, 
`Extent1`.`Id` AS `Id3`, 
@gp3 AS `C4`, 
`Extent2`.`Id` AS `Id4`, 
CASE WHEN (`Extent2`.`Id` IS  NULL) THEN (NULL)  ELSE (@gp4) END AS `C5`, 
`Extent2`.`Id` AS `Id5`, 
`Extent2`.`TreeId`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
NULL AS `C9`, 
NULL AS `C10`, 
NULL AS `C11`, 
NULL AS `C12`, 
NULL AS `C13`, 
NULL AS `C14`, 
NULL AS `C15`, 
NULL AS `C16`, 
NULL AS `C17`, 
NULL AS `C18`, 
NULL AS `C19`, 
NULL AS `C20`, 
NULL AS `C21`, 
NULL AS `C22`, 
NULL AS `C23`, 
NULL AS `C24`, 
NULL AS `C25`, 
NULL AS `C26`, 
NULL AS `C27`, 
NULL AS `C28`, 
NULL AS `C29`
FROM `Trees` AS `Extent1` LEFT OUTER JOIN `Other` AS `Extent2` ON `Extent1`.`Id` = `Extent2`.`TreeId`) UNION ALL (SELECT
2 AS `C1`, 
`Project2`.`Id`, 
`Project2`.`C1` AS `C2`, 
`Project2`.`Id` AS `Id1`, 
`Project2`.`ParentId`, 
`Project2`.`Id` AS `Id2`, 
`Project2`.`C2` AS `C3`, 
`Project2`.`Id` AS `Id3`, 
`Project2`.`C3` AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
`UnionAll1`.`Id` AS `C9`, 
`UnionAll1`.`C2` AS `C10`, 
`UnionAll1`.`Id1` AS `C11`, 
`UnionAll1`.`ParentId` AS `C12`, 
`UnionAll1`.`Id2` AS `C13`, 
`UnionAll1`.`C3` AS `C14`, 
`UnionAll1`.`Id3` AS `C15`, 
`UnionAll1`.`C4` AS `C16`, 
`UnionAll1`.`C1` AS `C17`, 
`UnionAll1`.`Id4` AS `C18`, 
`UnionAll1`.`C5` AS `C19`, 
`UnionAll1`.`Id5` AS `C20`, 
`UnionAll1`.`TreeId` AS `C21`, 
`UnionAll1`.`C6` AS `C22`, 
`UnionAll1`.`C7` AS `C23`, 
`UnionAll1`.`C8` AS `C24`, 
`UnionAll1`.`C9` AS `C25`, 
`UnionAll1`.`C10` AS `C26`, 
`UnionAll1`.`C11` AS `C27`, 
`UnionAll1`.`C12` AS `C28`, 
`UnionAll1`.`C13` AS `C29`, 
`UnionAll1`.`C14` AS `C30`, 
`UnionAll1`.`C15` AS `C31`, 
`UnionAll1`.`C16` AS `C32`
FROM (SELECT
`Extent3`.`Id`, 
`Extent3`.`ParentId`, 
@gp5 AS `C1`, 
@gp6 AS `C2`, 
@gp7 AS `C3`
FROM `Trees` AS `Extent3`) AS `Project2` LEFT OUTER JOIN ((SELECT
CASE WHEN (`Extent5`.`Id` IS  NULL) THEN (NULL)  ELSE (1) END AS `C1`, 
`Extent4`.`Id`, 
@gp8 AS `C2`, 
`Extent4`.`Id` AS `Id1`, 
`Extent4`.`ParentId`, 
`Extent4`.`Id` AS `Id2`, 
@gp9 AS `C3`, 
`Extent4`.`Id` AS `Id3`, 
@gp10 AS `C4`, 
`Extent5`.`Id` AS `Id4`, 
CASE WHEN (`Extent5`.`Id` IS  NULL) THEN (NULL)  ELSE (@gp11) END AS `C5`, 
`Extent5`.`Id` AS `Id5`, 
`Extent5`.`TreeId`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
NULL AS `C9`, 
NULL AS `C10`, 
NULL AS `C11`, 
NULL AS `C12`, 
NULL AS `C13`, 
NULL AS `C14`, 
NULL AS `C15`, 
NULL AS `C16`
FROM `Trees` AS `Extent4` LEFT OUTER JOIN `Other` AS `Extent5` ON `Extent4`.`Id` = `Extent5`.`TreeId`
 WHERE 1 = 1) UNION ALL (SELECT
2 AS `C1`, 
`Extent6`.`Id`, 
@gp12 AS `C2`, 
`Extent6`.`Id` AS `Id1`, 
`Extent6`.`ParentId`, 
`Extent6`.`Id` AS `Id2`, 
@gp13 AS `C3`, 
`Extent6`.`Id` AS `Id3`, 
@gp14 AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
`Join3`.`Id` AS `Id4`, 
@gp15 AS `C9`, 
`Join3`.`Id` AS `Id5`, 
`Join3`.`ParentId` AS `ParentId1`, 
`Join3`.`Id` AS `Id6`, 
@gp16 AS `C10`, 
CASE WHEN (`Join3`.`ID1` IS  NULL) THEN (NULL)  ELSE (1) END AS `C11`, 
`Join3`.`ID1` AS `Id7`, 
CASE WHEN (`Join3`.`ID1` IS  NULL) THEN (NULL)  ELSE (@gp17) END AS `C12`, 
`Join3`.`ID1` AS `Id8`, 
`Join3`.`TreeId`
FROM `Trees` AS `Extent6` INNER JOIN (SELECT
`Extent7`.`Id`, 
`Extent7`.`ParentId`, 
`Extent8`.`Id` AS `ID1`, 
`Extent8`.`TreeId`
FROM `Trees` AS `Extent7` LEFT OUTER JOIN `Other` AS `Extent8` ON `Extent7`.`Id` = `Extent8`.`TreeId`) AS `Join3` ON `Extent6`.`Id` = `Join3`.`ParentId`
 WHERE 1 = 1)) AS `UnionAll1` ON `Extent3`.`Id` = `UnionAll1`.`ParentId`)) AS `UnionAll2`
 ORDER BY 
`UnionAll2`.`Id1` ASC, 
`UnionAll2`.`C1` ASC, 
`UnionAll2`.`C8` ASC, 
`UnionAll2`.`C14` ASC, 
`UnionAll2`.`C21` ASC, 
`UnionAll2`.`C25` ASC

-- @gp1: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- @gp2: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp3: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp4: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- @gp5: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- @gp6: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp7: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp8: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- @gp9: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp10: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp11: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- @gp12: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- @gp13: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp14: 'Children' (Type = String, IsNullable = false, Size = 8)

-- @gp15: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- @gp16: 'Others' (Type = String, IsNullable = false, Size = 6)

-- @gp17: '870348ad-18a5-47db-9bae-fab05c423fcf' (Type = String, IsNullable = false, Size = 36)

-- Executing at 5/20/2015 10:01:38 AM +05:30

-- Failed in 166 ms with error: Unknown column 'Extent3.Id' in 'on clause'

Closed connection at 5/20/2015 10:01:38 AM +05:30