Within the SELECT statement, there are two nested SELECTs. The generated SQL will encounter a ORA-00904 : "invalid identifier" error with Oracle Database 11.2 since it has a restriction where it does not recognize outer select table alias "i" in the inner nested select query.

[kisshexuxia]

Steps to reproduce

C#

public async Task<IActionResult> GetBaseDept([FromQuery]ViewBaseDeptParameters param)
        {
            var sysUserInfo = await sysServices.GetSysInfo(User.Claims, param.Org_Code);
            if (!sysUserInfo.IsValid)
            {
                return BadRequest(ApiResult.GetResult40x(EnumResult400.Unauthorized, "您没有操作此接口的权限"));
            }

            var whereExpression = PredicateBuilder.New<Base_Dept>(true);

            if (!string.IsNullOrEmpty(param.Org_Code))
            {
                whereExpression = whereExpression.And(d => d.Sub_Org_Code == param.Org_Code);
            }
            else
            {
                whereExpression = whereExpression.And(d => d.Org_Code == sysUserInfo.MainOrgCode);
            }

            if (!string.IsNullOrEmpty(param.WorkNatureID))
            {
                var workList = await baseDeptPropertyClassServices.GetListAsync(d => d.WorkNatureID == param.WorkNatureID);

                var lists = workList.Select(d => d.DeptID).Distinct();
                whereExpression = whereExpression.And(d => lists.Contains(d.DeptID));
            }
            if (!string.IsNullOrEmpty(param.ServiceID))
            {

                var serviceList = await baseDeptPropertyClassServices.GetListAsync(d => 1 == 1);

                var lists = new List<string>();
                if (param.ServiceID == EnumHelper.GetEnumDescription(EnumServiceID.OutPatient))
                {
                    lists = serviceList.Where(d => d.ServiceID == EnumHelper.GetEnumDescription(EnumServiceID.OutPatient)
                    || d.ServiceID == EnumHelper.GetEnumDescription(EnumServiceID.OutAndInPatient)).Select(d => d.DeptID).Distinct().ToList();
                }
                else if (param.ServiceID == EnumHelper.GetEnumDescription(EnumServiceID.InPatient))
                {
                    lists = serviceList.Where(d => d.ServiceID == EnumHelper.GetEnumDescription(EnumServiceID.InPatient) ||
                    d.ServiceID == EnumHelper.GetEnumDescription(EnumServiceID.OutAndInPatient)).Select(d => d.DeptID).Distinct().ToList();
                }
                else
                {
                    lists = serviceList.Where(d => d.ServiceID == param.ServiceID).Select(d => d.DeptID).Distinct().ToList();
                }

                int i = lists.Count();
                whereExpression = whereExpression.And(d => lists.Contains(d.DeptID));
            }
            if (!string.IsNullOrEmpty(param.TreePID))
            {
                whereExpression = whereExpression.And(d => d.TreeID == param.TreePID || d.TreePID == param.TreePID);
            }
            whereExpression = whereExpression.GetWhereExpression<Base_Dept, ViewBaseDeptParameters>(param);

            PaginatedList<Base_Dept> data = await baseDeptServices.GetPageAsync((PaginationBase)param, whereExpression);

        public async Task<PaginatedList<T>> GetPageAsync(PaginationBase paginationBase, Expression<Func<T, bool>> whereExpression = null, bool isIgnoreQueryFilters = false)
        {
            if (whereExpression == null)
            {
                whereExpression = e => true;
            }
            if (paginationBase.PageIndex == 0)
            {
                whereExpression = e => false;
            }
            int index = paginationBase.PageIndex - 1;
            int size = paginationBase.PageSize;
            string orderBy = string.IsNullOrEmpty(paginationBase.OrderBy) ? "ID" : paginationBase.OrderBy;

            var iQueryable = _dbSet.Where(whereExpression);

            iQueryable = isIgnoreQueryFilters ? iQueryable.IgnoreQueryFilters() : iQueryable;

            var count = (await iQueryable.ToListAsync()).Count;
            var data = await iQueryable.OrderBy(orderBy).Skip(index * size).Take(size).ToListAsync();

            var pageList = new PaginatedList<T>(paginationBase.PageIndex, paginationBase.PageSize, count, data);

            return await Task.Run(() => pageList);
        }

`fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (30ms) [Parameters=[:p_2='0', :p_3='100'], CommandType='Text', CommandTimeout='0']
      Select 
       K0 "ID", K1 "CREATE_BY", K2 "CREATE_TIME", K3 "DEPTEXPLAIN", K4 "DEPTID", K5 "DEPTNAME", K6 "ISDEL", K7 "ISUPLOAD", K8 "ORG_CODE", K9 "PYM", K10 "POSITION", K11 "RECORDSTATE", K12 "SIMPLECODE", K13 "SUB_ORG_CODE", K14 "THEORDER", K15 "TREEID", K16 "TREEPID", K17 "UPDATE_BY", K18 "UPDATE_TIME", K19 "UPLOAD_TIME", K20 "WBM" from(
      select "m2".*, rownum r2 from
      (
      SELECT "m1"."ID" K0, "m1"."CREATE_BY" K1, "m1"."CREATE_TIME" K2, "m1"."DEPTEXPLAIN" K3, "m1"."DEPTID" K4, "m1"."DEPTNAME" K5, "m1"."ISDEL" K6, "m1"."ISUPLOAD" K7, "m1"."ORG_CODE" K8, "m1"."PYM" K9, "d"."POSITION" K10, "d"."RECORDSTATE" K11, "d"."SIMPLECODE" K12, "d"."SUB_ORG_CODE" K13, "d"."THEORDER" K14, "d"."TREEID" K15, "d"."TREEPID" K16, "d"."UPDATE_BY" K17, "d"."UPDATE_TIME" K18, "d"."UPLOAD_TIME" K19, "d"."WBM" K20
      FROM "BASE_DEPT" "d"
      WHERE (0 = 1)
      ORDER BY "d"."ID" NULLS FIRST
      ) "m2"
      ) "m1"
      where r2 > :p_2
      and r2 <= (:p_2 + :p_3)
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "m1"."PYM": invalid identifier`

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (11ms) [Parameters=[:param_Org_Code_0='41964590' (Size = 2000), :p_2='0', :p_3='100'], CommandType='Text', CommandTimeout='0']
      Select 
       K0 "ID", K1 "CREATE_BY", K2 "CREATE_TIME", K3 "DEPTEXPLAIN", K4 "DEPTID", K5 "DEPTNAME", K6 "ISDEL", K7 "ISUPLOAD", K8 "ORG_CODE", K9 "PYM", K10 "POSITION", K11 "RECORDSTATE", K12 "SIMPLECODE", K13 "SUB_ORG_CODE", K14 "THEORDER", K15 "TREEID", K16 "TREEPID", K17 "UPDATE_BY", K18 "UPDATE_TIME", K19 "UPLOAD_TIME", K20 "WBM" from(
      select "m2".*, rownum r2 from
      (
      SELECT "m1"."ID" K0, "m1"."CREATE_BY" K1, "m1"."CREATE_TIME" K2, "m1"."DEPTEXPLAIN" K3, "m1"."DEPTID" K4, "m1"."DEPTNAME" K5, "m1"."ISDEL" K6, "m1"."ISUPLOAD" K7, "d"."ORG_CODE" K8, "d"."PYM" K9, "d"."POSITION" K10, "d"."RECORDSTATE" K11, "d"."SIMPLECODE" K12, "d"."SUB_ORG_CODE" K13, "d"."THEORDER" K14, "d"."TREEID" K15, "d"."TREEPID" K16, "d"."UPDATE_BY" K17, "d"."UPDATE_TIME" K18, "d"."UPLOAD_TIME" K19, "d"."WBM" K20
      FROM "BASE_DEPT" "d"
      WHERE ((("d"."ISDEL" = 0)) AND (((("d"."SUB_ORG_CODE" = :param_Org_Code_0)) AND "d"."DEPTID" IN (N'0205', N'0201', N'0714', N'0711', N'0209', N'0214', N'0402', N'0203', N'0212', N'0208', N'0217', N'0404', N'0213', N'0401', N'0101', N'0501', N'0202', N'0712', N'0710', N'0210', N'0713', N'0716', N'0715', N'0204'))))
      ORDER BY "d"."ID" NULLS FIRST
      ) "m2"
      ) "m1"
      where r2 > :p_2
      and r2 <= (:p_2 + :p_3)
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "m1"."ISUPLOAD": 标识符无效

Further technical details

EF Core version: Database provider:Microsoft.EntityFrameworkCore.Oracle Oracle.ManagedDataAccess.Core (2.19.70) Oracle.EntityFrameworkCore(2.19.70) Target framework: NET Core 2.2 Operating system:windows 2012 R2 stand IDE: e.g. Visual Studio 2019 16.4.1

[ajcvickers]

@kisshexuxia This is an issue with the Oracle EF Core database provider. Please report it directly to them.