linq多表查询

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Otsuka.Application.Dal;
using Otsuka.Application.Bll.Common;
using Otsuka.Application.Common;
using Otsuka.Application.Bll.Dialog.ApplicationFind;

namespace Otsuka.Application.Bll.Dialog.ApplicationFind
{
    public class SearchApplicationBll : BllBaseEx
    {
        /// <summary>
        /// 要求キー-申請検索条件
        /// </summary>
        public const string REQUEST_KEY_APPLICATION_REQUEST = "APPLICATION_REQUEST";

        /// <summary>
        /// 返却キー-申請情報（複数件）
        /// </summary>
        public const string RESPONSE_KEY_APPLICATION_LIST = "APPLICATION_LIST";

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        protected override Dictionary<string, object> Perform()
        {
            // 検索条件の取得
            SearchApplicationRequest request = (SearchApplicationRequest)Request[REQUEST_KEY_APPLICATION_REQUEST];

            var query = DataContext.TBAATR_APPLICATION.AsQueryable();
            var productsQuery = DataContext.TBAATR_PRODUCT.AsQueryable();

            if (!CheckUtility.IsEmpty(request.ApplicationNo))
            {
                // 申請番号
                query = query.Where(item => item.APPLI_NO == request.ApplicationNo);
                productsQuery = productsQuery.Where(item => item.APPLI_NO == request.ApplicationNo);
            }

            if (!CheckUtility.IsEmpty(request.MaterialsInfoCode))
            {
                // 資材媒体
                query = query.Where(item => item.MATERIALS_CODE == request.MaterialsInfoCode);
            }

            if (!CheckUtility.IsEmpty(request.MaterialsClassInfoCode))
            {
                // 資材分類
                query = query.Where(item => item.MATERIALS_CLASS_CODE == request.MaterialsClassInfoCode);
            }

            if (!CheckUtility.IsEmpty(request.UniformCode))
            {
                // 統一コード
                query = query.Where(item => item.UNIFORM_CODE == request.UniformCode);
            }

            if (!CheckUtility.IsEmpty(request.OrderPartCode))
            {
                // 発注品番
                query = query.Where(item => item.ORDER_PART_CODE == request.OrderPartCode);
            }

            if (!CheckUtility.IsEmpty(request.ApplicantDivisionCode))
            {
                // 申請部署
                query = query.Where(item => item.APPLICANT_DIVISION == request.ApplicantDivisionCode);
            }

            if (!CheckUtility.IsEmpty(request.ApplicantStaffCode))
            {
                // 申請者
                query = query.Where(item => item.APPLICANT_STF_CODE == request.ApplicantStaffCode);
            }

            if (!CheckUtility.IsEmpty(request.ApplicationDateStart))
            {
                // 申請日開始
                query = query.Where(item => item.APPLICATION_DATE.CompareTo(request.ApplicationDateStart.Replace("/", "") + "00") >= 0);
            }

            if (!CheckUtility.IsEmpty(request.ApplicationDateEnd))
            {
                // 申請日終了
                query = query.Where(item => item.APPLICATION_DATE.CompareTo(request.ApplicationDateEnd.Replace("/", "") + "99") <= 0);
            }

            if (!CheckUtility.IsEmpty(request.DrugCode))
            {
                // 薬剤
                query = query.Where(item => item.DRUG == request.DrugCode);
            }

            if (!CheckUtility.IsEmpty<string>(request.ProductCodes))
            {
                // 製品
                productsQuery = productsQuery.Where(item => request.ProductCodes.Contains(item.PROD_GRP_CODE));
            }

            if (!CheckUtility.IsEmpty(request.MaterialsName))
            {
                // 資材名称
                query = query.Where(item => item.MATERIALS_NM_KNJ.Contains(request.MaterialsName));
            }

            if (!CheckUtility.IsEmpty(request.Mode))
            {
                if (request.Mode == ConstValue.APPLICATION_FIND_MODE_APPLICATION)
                {
                    // ステータス(公開許可)
                    query = query.Where(item => item.STATUS_NO == ((int)Status.SecretariatPermitPublic).ToString());
                }
                else if (request.Mode == ConstValue.APPLICATION_FIND_MODE_COMMITTEE)
                {
                    // ステータス(受理、審議Ⅰ、Ⅱ、事前審議)
                    query = query.Where(item => new[] { 
                        ((int)Status.SecretariatAccept).ToString(), 
                        ((int)Status.SecretariatAcceptDiscuss).ToString(), 
                        ((int)Status.SecretariatAcceptNoDiscuss).ToString() 
                    }.Contains(item.STATUS_NO));
                }
            }


            //ADD:SCSK:20141128:IT指摘事項No.16:START
            string yyyymmdd = DateTime.Now.ToString("yyyyMMdd");
            //ADD:SCSK:20141128:IT指摘事項No.16:END

            List<SearchApplicationResult> results = (from row in query.Where(item => item.DEL_FLG == '0')
                                                     //MOD:SCSK:20141128:IT指摘事項No.16:START
                                                     //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()
                                                     //MOD:SCSK:20141128:IT指摘事項No.16:END
                                                     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()
                                                     //ADD:SCSK:20141128:IT指摘事項No.16:START
                                                     from committeeApplication in DataContext.TBAARS_COMMITTEE_APPLICATION
                                                     //ADD:SCSK:20141128:IT指摘事項No.16:END
                                                     where
                                                     materialsInfo.DEL_FLG == '0'
                                                     && materialsClassInfo.DEL_FLG == '0'
                                                     //ADD:SCSK:20141128:IT指摘事項No.16:START
                                                     && divisions.STA_YMD.CompareTo(yyyymmdd) <= 0
                                                     && divisions.END_YMD.CompareTo(yyyymmdd) >= 0
                                                     && row.APPLI_NO == committeeApplication.APPLI_NO
                                                     && row.REVISION_NO == committeeApplication.REVISION_NO
                                                     && committeeApplication.DEL_FLG == '0'
                                                     //ADD:SCSK:20141128:IT指摘事項No.16:END
                                                     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>();

            List<SearchApplicationResult> searchResults = new List<SearchApplicationResult>();

            foreach (SearchApplicationResult result in results)
            {
                if (((result.CommitteeNo != null && result.CommitteeDelFlg == '0') && !request.IsContainSettedCommittee) || result.AbolitionFlg == "1")
                {
                    // 委員会設定済み申請を検索に含めないときで、委員会番号がnull のとき
                    continue;
                }

                if (CheckUtility.IsEmpty(result.ApplicantStaffName)
                    || (!CheckUtility.IsEmpty(result.StaffStaYmd)
                    && (result.StaffStaYmd.CompareTo(result.ApplicationDivRegDate) <= 0
                    && result.StaffEndYmd.CompareTo(result.ApplicationDivRegDate) >= 0)))
                {
                    // 申請者が空文字、もしくは有効な申請者が存在する
                    result.DiscussionClassName = this.GetDiscussionClassName(result.DiscussionClassName);
                    searchResults.Add(result);
                }
            }

            Dictionary<string, object> response = new Dictionary<string, object>();
            response.Add(RESPONSE_KEY_APPLICATION_LIST, searchResults);

            return response;
        }

        /// <summary>
        /// 審議種別コードから審議種別名を朱得する
        /// </summary>
        /// <param name="discussionClassCode"></param>
        /// <returns></returns>
        private string GetDiscussionClassName(string discussionClassCode)
        {
            if (discussionClassCode == "1")
            {
                return "審議Ⅰ";
            }
            else if (discussionClassCode == "2")
            {
                return "審議Ⅱ";
            }
            else if (discussionClassCode == "3")
            {
                return "事前審議";
            }
            else if (discussionClassCode == "0")
            {
                return "未判定";
            }

            return string.Empty;
        }
    }
}