c# - How can I properly compose this sql subquery to values from another table instead of hard coding them? -

not sure if worded question correctly, have huge sql server handicap, may simple thing not seeing. have following query:

select  stu.systudentid, stu.firstname,  stu.lastname,  stu.startdate, enr.adprogramversionid, enr.adcatalogyearid, enr.gpa cumgpa, case when (stu.datelstmod > enr.datelstmod)    stu.datelstmod    else enr.datelstmod  end datelstmod    systudent stu   inner join adenroll enr on enr.systudentid = stu.systudentid   inner join syschoolstatus schsta on schsta.syschoolstatusid = enr.syschoolstatusid   inner join systatus systa on systa.systatusid = schsta.systatusid         systa.category in ('e','a')          , stu.sycampusid = 6         ,          (              enr.adprogramversionid null              or              (enr.adprogramversionid = '52' , enr.adcatalogyearid='3')             or             (enr.adprogramversionid='53' , enr.adcatalogyearid='3')             or             (enr.adprogramversionid='50' , enr.adcatalogyearid='3')             or             (enr.adprogramversionid='51' , enr.adcatalogyearid='3')             or             (enr.adprogramversionid='52' , enr.adcatalogyearid='4')             or             (enr.adprogramversionid='53' , enr.adcatalogyearid='4')             or             (enr.adprogramversionid='50' , enr.adcatalogyearid='4')             or             (enr.adprogramversionid='51' , enr.adcatalogyearid='4')             or             (enr.adprogramversionid='54' , enr.adcatalogyearid='4')         ) 

the values in nested 'and' clause can pulled database itself. condition value this:

select adprogramversionid adenroll adcatalogyearid not null 

but if try put in rather list of or clauses different result set. there way accomplish need (replace hard coded numbers retrieval of number table)? subqueries? weird joins? black magic?

this code trying replace straight sql query:

var query = @"         select            stu.systudentid,           stu.firstname,            stu.lastname,            stu.startdate,           enr.adprogramversionid,           enr.adcatalogyearid,           enr.gpa cumgpa,           case when (stu.datelstmod > enr.datelstmod)              stu.datelstmod              else enr.datelstmod            end datelstmod                    systudent stu           inner join adenroll enr on enr.systudentid = stu.systudentid           inner join syschoolstatus schsta on schsta.syschoolstatusid = enr.syschoolstatusid           inner join systatus systa on systa.systatusid = schsta.systatusid         systa.category in ('e','a')          , stu.sycampusid = 6          ,          (             enr.adprogramversionid in              ("                 + string.join(",", dc.catalogprograms.where(cp => cp.catalog.cvadcatalogyearid == null).select(cp => cp.cvadprogramversionid)) + @"             )             or             (";              // students in program versions use catalogs               var andclauses = new list<string>();             foreach (var catprog in dc.catalogprograms.where(cp => cp.catalog.cvadcatalogyearid != null))                 andclauses.add("(enr.adprogramversionid=" + catprog.cvadprogramversionid + " , enr.adcatalogyearid=" + catprog.catalog.cvadcatalogyearid + ")");              query += string.join("\n or", andclauses) + @"              )         )"; 

the reason want rid of code not want mix linq-to-sql in new solution, using import new database design altogether using ef orm.

you can incorporate adenroll clause , simplify follows. edits in caps.

from    systudent stu   inner join adenroll enr on enr.systudentid = stu.systudentid   inner join syschoolstatus schsta on schsta.syschoolstatusid = enr.syschoolstatusid   inner join systatus systa on systa.systatusid = schsta.systatusid   left outer join adenroll ae on enr.adprogramversionid = ae.adprogramversionid systa.category in ('e','a')  , stu.sycampusid = 6 ,  (      enr.adprogramversionid null      or      ae.adcatalogyearid not null )