LINQ query projection using non-required reference navigation property queries all columns in tables

[jemiller0]

Steps to reproduce

Define a class that has a reference navigation property that is not required. The SupervisorUser property below is an example because it, or the foreign key property SupervisorUserId property that corresponds to it doesn't have a Required attribute.

    [DisplayColumn("Name"), ScaffoldTable(true), Table("ca_account_t", Schema="ca")]
    public partial class Account
    {
...
        [Display(Order = 1)]
        public virtual Chart Chart { get; set; }

        [Column("FIN_COA_CD", Order = 2), Display(Name = "Chart", Order = 2), Key, Required, StringLength(2)]
        public virtual string ChartCode { get; set; }
...
        [Display(Name = "Supervisor User", Order = 10)]
        public virtual User SupervisorUser { get; set; }

        [Column("ACCT_SPVSR_UNVL_ID"), Display(Name = "Supervisor User", Order = 11), StringLength(40)]
        public virtual string SupervisorUserId { get; set; }
...
    }

Execute a LINQ query such as the following which does a projection, selecting only one property from the related entity through the navigation property.

var l = oc.Accounts.Select(a => new { a.Name, a.SupervisorUser.UserName } ).ToArray();

This results in the following SQL being executed in the database.

Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='90']
SELECT `a`.`FIN_COA_CD`, `a`.`ACCOUNT_NBR`, `a`.`ACCT_TYP_CD`, `a`.`BDGT_REC_LVL_CD`, `a`.`ACCT_PHYS_CMP_CD`, `a`.`CG_CFDA_NBR`, `a`.`ACCT_CITY_NM`, `a`.`ACCT_CLOSED_IND`, `a`.`CONT_FIN_COA_CD`, `a`.`CONT_ACCOUNT_NBR`, `a`.`CONTR_CTRL_FCOA_CD`, `a`.`CONTR_CTRLACCT_NBR`, `a`.`CG_ACCT_RESP_ID`, `a`.`ACCT_CREATE_DT`, `a`.`AC_CSTM_ICREXCL_CD`, `a`.`ACCT_EXPIRATION_DT`, `a`.`ENDOW_FIN_COA_CD`, `a`.`ENDOW_ACCOUNT_NBR`, `a`.`FIN_EXT_ENC_SF_CD`, `a`.`FIN_PRE_ENC_SF_CD`, `a`.`FIN_OBJ_PRSCTRL_CD`, `a`.`ACCT_FSC_OFC_UID`, `a`.`ACCT_FRNG_BNFT_CD`, `a`.`OBJ_ID`, `a`.`FIN_HGH_ED_FUNC_CD`, `a`.`ACCT_IN_FP_CD`, `a`.`INCOME_FIN_COA_CD`, `a`.`INCOME_ACCOUNT_NBR`, `a`.`ICR_FIN_COA_CD`, `a`.`ICR_ACCOUNT_NBR`, `a`.`FIN_SERIES_ID`, `a`.`ACCT_ICR_TYP_CD`, `a`.`FIN_INT_ENC_SF_CD`, `a`.`LBR_BEN_RT_CAT_CD`, `a`.`ACCT_MGR_UNVL_ID`, `a`.`ACCOUNT_NM`, `a`.`OLE_UNIV_ACCT_NBR`, `a`.`ACCT_OFF_CMP_IND`, `a`.`ORG_CD`, `a`.`ACCT_PND_SF_CD`, `a`.`ACCT_ZIP_CD`, `a`.`RPTS_TO_FIN_COA_CD`, `a`.`RPTS_TO_ACCT_NBR`, `a`.`ACCT_RSTRC_STAT_CD`, `a`.`ACCT_RSTRC_STAT_DT`, `a`.`ACCT_EFFECT_DT`, `a`.`ACCT_STATE_CD`, `a`.`ACCT_STREET_ADDR`, `a`.`SUB_FUND_GRP_CD`, `a`.`ACCT_SF_CD`, `a`.`ACCT_SPVSR_UNVL_ID`, `a`.`THRESHOLD_AMOUNT`, `a`.`THRESHOLD_PERCENTAGE`, `a`.`VNDR_HDR_GNRTD_ID`, `a`.`VNDR_DTL_ASND_ID`, `a`.`VER_NBR`, `a.SupervisorUser`.`PRNCPL_ID`, `a.SupervisorUser`.`ACTV_IND`, `a.SupervisorUser`.`OBJ_ID`, `a.SupervisorUser`.`LAST_UPDT_DT`, `a.SupervisorUser`.`PRNCPL_PSWD`, `a.SupervisorUser`.`ENTITY_ID`, `a.SupervisorUser`.`PRNCPL_NM`, `a.SupervisorUser`.`VER_NBR`
FROM `ca_account_t` AS `a`
LEFT JOIN `krim_prncpl_t` AS `a.SupervisorUser` ON `a`.`ACCT_SPVSR_UNVL_ID` = `a.SupervisorUser`.`PRNCPL_ID`
ORDER BY `a`.`ACCT_SPVSR_UNVL_ID`

As you can see, it appears that all columns from the related table, as well as the main table, are returned in the query. I would have expected it to only return the column for the one property that was specified in the projection from that table, plus the one from the main table. I.e. the a.ACCOUNT_NM and a.SupervisorUser.PRNCPL_NM columns.

EntityFrameworkCoreProjectionBug.zip
If I run the following LINQ query that does the same thing, but, using a reference navigation property that is required, it does only query for the column that was specified in the projection.

var l = oc.Accounts.Select(a => new { a.Name, ChartName = a.Chart.Name } ).ToArray();

This results in the following SQL.

Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='90']
SELECT `a`.`ACCOUNT_NM`, `a.Chart`.`FIN_COA_DESC`
FROM `ca_account_t` AS `a`
INNER JOIN `ca_chart_t` AS `a.Chart` ON `a`.`FIN_COA_CD` = `a.Chart`.`FIN_COA_CD`

I don't know for sure, but, I'm guessing that the difference is that one reference property is required and the other isn't which results in either a LEFT JOIN or an INNER JOIN. The INNER JOIN case works as expected, but, the LEFT JOIN case results in pulling in extra columns. If you have tables with a lot of tables, this can significantly slow the queries down. Especially if you have CLOB fields, etc.

The issue

Extra columns are queried for in the generated SQL for non-required reference navigation properties in LINQ queries that are selecting specific fields.

Further technical details

EF Core version: 1.1.0
Operating system: Windows 10 Anniversary Update
Visual Studio version: 2015

Other details about my project setup: .NET Framework 4.6.2

[rowanmiller]

@smitpatel can you find the dupe and then close

[smitpatel]

Dupe of #6647