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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
dennismoore
Posts: 3
Joined: Sun 10 Jan 2016 16:10

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

Post by dennismoore » Mon 18 Jan 2016 16:00

We have the following model:
Image
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
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Tue 19 Jan 2016 16:42

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
Posts: 3
Joined: Sun 10 Jan 2016 16:10

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

Post by dennismoore » Wed 20 Jan 2016 09:56

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
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Wed 20 Jan 2016 13:27

dennismoore wrote:Should we report this as an issue to the EF team at http://entityframework.codeplex.com ?
Yes, you should.

dennismoore
Posts: 3
Joined: Sun 10 Jan 2016 16:10

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

Post by dennismoore » Wed 27 Jan 2016 17:23

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

Post Reply