EF使用sql语句

https://www.cnblogs.com/chenwolong/p/SqlQuery.html

https://blog.csdn.net/zdhlwt2008/article/details/77832858

public static IQueryable<DAL.ZTHR.ZTHROrgUnit> QueryCTESub(string id, Chaolj.DAL.ZTHR.ZTHRContext db)
        {
            IQueryable<DAL.ZTHR.ZTHROrgUnit> qry = null;

            // 1. 先根据Id 查询出编码；
            var model = Get(id);
            if (model == null) return null;
            string no = Get(id).No;
            // 2. 再根据No构建sql语句

            string cteSql = @"WITH org(""Id"",""No"",""Name"",""ParentNo"",""LeaderId"",""Leader"",""Comment"",""Sort"",""OrgTypeNo"",""OrgType"",""CreateUser"",""CreateTime"",""UpdateUser"",""UpdateTime"") AS "
                            + "(select "
                            + @"""Id"",""No"",""Name"",""ParentNo"",""LeaderId"",""Leader"",""Comment"",""Sort"",""OrgTypeNo"",""OrgType"",""CreateUser"",""CreateTime"",""UpdateUser"",""UpdateTime"" FROM ""ZTHROrgUnit"" WHERE ""No""='" + no + "' "
                            + @"UNION ALL "
                            + @"SELECT z.""Id"",z.""No"",z.""Name"",z.""ParentNo"",z.""LeaderId"",z.""Leader"",z.""Comment"",z.""Sort"",z.""OrgTypeNo"",z.""OrgType"",z.""CreateUser"",z.""CreateTime"",z.""UpdateUser"",z.""UpdateTime"" FROM ""ZTHROrgUnit"" z "
                            + @"INNER JOIN org o ON z.""ParentNo"" = o.""No"")"
                            + @"SELECT * FROM org ORDER BY ""ParentNo""";
            qry = db.ZTHROrgUnit.SqlQuery(cteSql).AsQueryable(); // .ToList();

            return qry;
        }