Many to Many Entity Framework 6.0 Relationships Without Extra Nesting

So you've got a many to many (m2m) relationship. And you'd like the Entity Framework to return both ends of this relationship using SQL that isn't overly nested/CASE-d/UnionAll-d.

Let's take the canonical m2m relationship of Authors to Books. An Author can write many Books and a Book can be written by many Authors.

An Author

Many Books

Depending on how well the Entity Framework can figure out the multiplicity of each end you may end up with SQL that's very inefficient.

A human would query this with LEFT OUTER JOINs.

But given the following C# code (LINQ to Entities, query syntax):

from a in AUTHORS
select new { a, a.BOOKS }

The generated SQL has too much nesting and aliasing:

SELECT 
"Project1"."C1" AS "C1", 
"Project1"."AUTHOR_ID" AS "AUTHOR_ID", 
"Project1"."FNAME" AS "FNAME", 
"Project1"."LNAME" AS "LNAME", 
"Project1"."C2" AS "C2", 
"Project1"."BOOK_ID" AS "BOOK_ID", 
"Project1"."NAME" AS "NAME", 
"Project1"."DESCRIPTION" AS "DESCRIPTION"
FROM ( SELECT 
 "Extent1"."AUTHOR_ID" AS "AUTHOR_ID", 
 "Extent1"."FNAME" AS "FNAME", 
 "Extent1"."LNAME" AS "LNAME", 
 1 AS "C1", 
 "Join1"."BOOK_ID1" AS "BOOK_ID", 
 "Join1"."NAME" AS "NAME", 
 "Join1"."DESCRIPTION" AS "DESCRIPTION", 
 CASE WHEN ("Join1"."BOOK_ID2" IS NULL) THEN NULL ELSE 1 END AS "C2"
 FROM  "SCOTT"."AUTHORS" "Extent1"
 LEFT OUTER JOIN  (SELECT "Extent2"."BOOK_ID" AS "BOOK_ID2", "Extent2"."AUTHOR_ID" AS "AUTHOR_ID", "Extent3"."BOOK_ID" AS "BOOK_ID1", "Extent3"."NAME" AS "NAME", "Extent3"."DESCRIPTION" AS "DESCRIPTION"
  FROM  "SCOTT"."AUTHOR_BOOKS" "Extent2"
  INNER JOIN "SCOTT"."BOOKS" "Extent3" ON "Extent3"."BOOK_ID" = "Extent2"."BOOK_ID" ) "Join1" ON "Extent1"."AUTHOR_ID" = "Join1"."AUTHOR_ID"
)  "Project1"
ORDER BY "Project1"."AUTHOR_ID" ASC, "Project1"."C2" ASC

This holds true even if eager loading (.Include("BOOKS")) is used. It would be nice to use a LEFT OUTER JOIN here without the extra nesting.

Modified C# to eliminate nesting:

from a in AUTHORS
from b in a.BOOKS.DefaultIfEmpty()
select new { a, b }

Eliminates the extra level of nesting in the resulting SQL:

SELECT 
1 AS "C1", 
"Extent1"."AUTHOR_ID" AS "AUTHOR_ID", 
"Extent1"."FNAME" AS "FNAME", 
"Extent1"."LNAME" AS "LNAME", 
"Join1"."BOOK_ID1" AS "BOOK_ID", 
"Join1"."NAME" AS "NAME", 
"Join1"."DESCRIPTION" AS "DESCRIPTION"
FROM  "SCOTT"."AUTHORS" "Extent1"
LEFT OUTER JOIN  (SELECT "Extent2"."BOOK_ID" AS "BOOK_ID2", "Extent2"."AUTHOR_ID" AS "AUTHOR_ID", "Extent3"."BOOK_ID" AS "BOOK_ID1", "Extent3"."NAME" AS "NAME", "Extent3"."DESCRIPTION" AS "DESCRIPTION"
 FROM  "SCOTT"."AUTHOR_BOOKS" "Extent2"
 INNER JOIN "SCOTT"."BOOKS" "Extent3" ON "Extent3"."BOOK_ID" = "Extent2"."BOOK_ID" ) "Join1" ON "Extent1"."AUTHOR_ID" = "Join1"."AUTHOR_ID"

This example is based on using Entity Framework 6.0 with Oracle's ODP.NET Managed Entity Framework driver. That driver, in turn, depends on the ODP.NET Managed Framework driver (strangely enough, at least at first glance, these are not the same thing).