List<SearchApplicationResult> results = (from row in query.Where(item => item.DEL_FLG == '0')
join row_temp in DataContext.TBAARS_COMMITTEE_APPLICATION.AsQueryable().Where(item => item.DEL_FLG == '0') on row.APPLI_NO equals row_temp.APPLI_NO into row_temp2
from committeeApplication in row_temp2.DefaultIfEmpty()
join products_temp in productsQuery.Where(item => item.DEL_FLG == '0') on row.APPLI_NO equals products_temp.APPLI_NO into products_temp2
from products in products_temp2.DefaultIfEmpty()
join staffs_temp in DataContext.TBCMRS_STF_INFO on row.APPLICANT_STF_CODE equals staffs_temp.STF_CODE into staffs_temp2
from staffs in staffs_temp2.DefaultIfEmpty()
join divisions_tmp in DataContext.TBCMRS_CST_INFO on row.APPLICANT_DIVISION equals divisions_tmp.CST_CODE into divisions_temp2
from divisions in divisions_temp2.DefaultIfEmpty()
join drugs_temp in DataContext.TBCMRS_DRUG_INFO.AsQueryable().Where(item => item.DEL_FLG == '0') on row.DRUG equals drugs_temp.DRUG_CODE into drugs_temp2
from drugs in drugs_temp2.DefaultIfEmpty()
join materialsInfo in DataContext.MATERIALS_INFO on row.MATERIALS_CODE equals materialsInfo.MATERIALS_CODE
join materialsClassInfo in DataContext.MATERIALS_CLASS_INFO on row.MATERIALS_CLASS_CODE equals materialsClassInfo.MATERIALS_CLASS_CODE
//join materialsattributes_temp in DataContext.MATERIALS_ATTRIBUTES.AsQueryable().Where(item => item.ABOLITION_FLG != "1") on row.MATERIALS_NO equals materialsattributes_temp.MATERIALS_NO into materialsattributes_temp2
join materialsattributes_temp in DataContext.MATERIALS_ATTRIBUTES on row.MATERIALS_NO equals materialsattributes_temp.MATERIALS_NO into materialsattributes_temp2
from materialsattributes in materialsattributes_temp2.DefaultIfEmpty()
where
materialsInfo.DEL_FLG == '0'
&& materialsClassInfo.DEL_FLG == '0'
select new SearchApplicationResult()
{
ApplicationNo = row.APPLI_NO,
ApplicantDivisionName = divisions.SCN_NM_KNJ,
ApplicantStaffName = staffs.STF_NM_KNJ,
ApplicationDate = row.APPLICATION_DATE,
DrugName = drugs.DRUG_NM_KNJ,
MaterialsInfoName = materialsInfo.MATERIALS_NM_KNJ,
MaterialsClassInfoName = materialsClassInfo.MATERIALS_CLASS_NM_KNJ,
MaterialsName = row.MATERIALS_NM_KNJ,
DiscussionClassName = row.DISCUSSION_CLASS.ToString(),
UniformCode = row.UNIFORM_CODE,
OrderPartCode = row.ORDER_PART_CODE,
CommitteeNo = committeeApplication.COMMITTEE_NO,
CommitteeDelFlg = committeeApplication.DEL_FLG,
StaffStaYmd = divisions.STA_YMD,
StaffEndYmd = divisions.END_YMD,
ApplicationDivRegDate = row.APPLICANT_DIV_REG_DATE,
AbolitionFlg = materialsattributes.ABOLITION_FLG,
//ADD:SCSK:20141028:2-3:START
PurposeBackground = row.PURPOSE_BACKGROUND
//ADD:SCSK:20141028:2-3:END
}).Distinct().ToList<SearchApplicationResult>();