Tony

var query = from left in 左
            join right in 右
            on left.欄位 equals right.欄位 into subGroup
            from right in subGroup.Where(x=>x.合併前的條件篩選1)
            .Where(x=>x.合併前的條件篩選2)
            .Where(x=>x.合併前的條件篩選3)
            .DefaultIfEmpty()
            
1.使用.DefaultIfEmpty()關鍵字進行join
2.Where條件要下再.DefaultIfEmpty()之前，否則Linq有可能因為where條件，產出的SQL會變成inner join


var fundStatus = ((int)ETFFundStatus.發行).ToString();
var fundType = ((int)ETFFundType.股票型).ToString();
var fundValueProClaim = "Y";
var fundValueType = FundValueType.ETF基金;
var start = startDate.ToShortTaiwanDate();
var end = endDate.ToShortTaiwanDate();

// Join EC Fund
var fundQuery = from pitcfund in db.PITC_ETF_Fund
                join ecfund in db.EC_ETF_FND
                on pitcfund.sFundCode equals ecfund.FUND_CODE into g
                from ecfund in g.Where(x => x.FUND_STATUS == fundStatus)
                .Where(x => x.FUND_TYPE == fundType)
                .DefaultIfEmpty()
                select new { pitcfund, ecfund };
// Join ETF Value
var query = from fund in fundQuery
            join value in db.EC_ETF_VALUE
            on fund.ecfund.FUND_CODE equals value.FUND_CODE into g
            from value in g.Where(x=>x.PROCLAIM_YN== fundValueProClaim)
            .Where(x => x.TYPE == fundValueType)
            .Where(x => string.Compare(x.TRAN_DATE, start) >= 0)
            .Where(x => string.Compare(x.TRAN_DATE, end) <= 0)
            .DefaultIfEmpty()

            select new
            {
                fund.pitcfund,
                fund.ecfund,
                value
            };