Generated SQL depends on the order of property setters in the materialized type

[dennismoore]

We have the following model:

Note that VWMACSKA is a view, and the association between VWMACSKA and MACSKA is only a conceptual level association.

We use EF 6.1.3 with Dotconnect for Oracle 8.5.563.6

The following query

Code: Select all

 var zzz = ctx.MACSKAs.AsNoTracking()
    .Select(
        e => new
        {
            e.VWMACSKAVALIDITY.MCSVALIDFROM,
            e.ZEBRA.NAME                     
        }
    ).Take(5).ToList();

generates

Code: Select all

SELECT 
"Limit1".C1,
"Limit1".MCSVALIDFROM,
"Limit1".NAME
FROM ( SELECT 
	"Extent2".MCSVALIDFROM,
	"Extent3".NAME,
	1 AS C1
	FROM   IPREF.MACSKA "Extent1"
	LEFT OUTER JOIN IPREF.VW_MACSKA_VALIDITY "Extent2" ON "Extent1".MACSKAID = "Extent2".MACSKAID
	INNER JOIN IPREF.ZEBRA "Extent3" ON "Extent1".ZEBRAID = "Extent3".ZEBRAID
	WHERE  ROWNUM <= 5
)  "Limit1"

but when we reorder the properties in the anonymous class

Code: Select all

  ctx.MACSKAs.AsNoTracking()
                    .Select(
                        e=>new
                        {                            
                            e.ZEBRA.NAME,
                            e.VWMACSKAVALIDITY.MCSVALIDFROM,
                        }
                    ).Take(5).ToList();

it generates

Code: Select all

SELECT 
"Limit1".C1,
"Limit1".NAME,
"Limit1".MCSVALIDFROM
FROM ( SELECT 
	"Extent3".NAME,
	"Extent4".MCSVALIDFROM,
	1 AS C1
	FROM    IPREF.MACSKA "Extent1"
	LEFT OUTER JOIN IPREF.VW_MACSKA_VALIDITY "Extent2" ON "Extent1".MACSKAID = "Extent2".MACSKAID
	INNER JOIN IPREF.ZEBRA "Extent3" ON "Extent1".ZEBRAID = "Extent3".ZEBRAID
	LEFT OUTER JOIN IPREF.VW_MACSKA_VALIDITY "Extent4" ON "Extent1".MACSKAID = "Extent4".MACSKAID
	WHERE  ROWNUM <= 5
)  "Limit1"

Note the extra join of VW_MACSKA in the latter test case. Why is the generated SQL different for the EF queries above?

We can provide a sample project here: https://www.dropbox.com/s/b1novxcq5a7tv ... t.zip?dl=0

[Shalex]

You have encountered a lack of optimization in Entity Framework engine (extra join is generated with System.Data.SqlClient as well).

There is a workaround: right click the MACSKA_VWMACSKAVALIDITY association > Create Storage and Mapping to create the corresponding FK constraint in SSDL. There is no extra join in this case.

[dennismoore]

Thanks, I can confirm that the workaround works.
Should we report this as an issue to the EF team at http://entityframework.codeplex.com ?

[Shalex]

Should we report this as an issue to the EF team at http://entityframework.codeplex.com ?

Yes, you should.

[dennismoore]

Issue created (with MS SQL sample): https://entityframework.codeplex.com/workitem/2877
Thanks for your support.