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

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

Postby 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/b1novxcq5a7tvox/EFDevart.zip?dl=0
dennismoore
 
Posts: 3
Joined: Sun 10 Jan 2016 16:10

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

Postby 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.
Shalex
Devart Team
 
Posts: 7709
Joined: Thu 14 Aug 2008 12:44

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

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

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

Postby 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.
Shalex
Devart Team
 
Posts: 7709
Joined: Thu 14 Aug 2008 12:44

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

Postby dennismoore » Wed 27 Jan 2016 17:23

Issue created (with MS SQL sample): https://entityframework.codeplex.com/workitem/2877
Thanks for your support.
dennismoore
 
Posts: 3
Joined: Sun 10 Jan 2016 16:10


Return to dotConnect for Oracle