Left Outer Join Problem in Linq-To-Sql

Hi I have 5 tables. The necessary relationships are as follows

Period Id StartDate EndDate IsDeleted

Code Id Name

YearlyTarget Id CodeId PeriodId YTAmount IsDeleted

AlteredTarget Id YearlyTargetId AltAmount IsDeleted

Actual Id AlteredTargetId ActualAmount IsDeleted

I have 4 quarters in year data. YearlyTarget exists for all quarters, AlteredTarget for Q1 and Actual for none.

My query is as follows:

from cl in this.Context.Codes    
join ytl in this.Context.YearlyTargets on cl.Id equals ytl.CodeId    
join pl in this.Context.Periods on ytl.PeriodId equals pl.Id    
join atl in this.Context.AlteredTargets on ytl.Id equals cdpl.YearlyTargetId into ccl    
join al in this.Context.Actuals on ytl.Id  equals al.AlteredTargets.YearlyTargetId into cal   
from cc in ccl.DefaultIfEmpty()    
from ca in cal.DefaultIfEmpty()
where cc.IsDeleted == false && ca.IsDeleted == false   
select new    
{    
    Year = pl.EndDate.Year,    
    PeriodType = (开发者_运维知识库PeriodType)pl.PeriodType,    
    PeriodName = pl.StartDate,    
    CodeName = cl.CodeName,    
    YT = ytl.TargetAmount,    
    CDP = cc.AltAmount,    
    Actual = ca.ActualAmount    
};

the query returns empty. Can someone please tell me what's wrong with the query. Thanks!!!

My guess is that it's the where clause which is messing you up. Don't forget that cc and ca can be null. Try changing the where clause to:

where (cc == null || !cc.IsDeleted) && (ca == null || !ca.IsDeleted)

You may also then need to change the projections where you use cc and ca to:

CDP = cc == null ? 0 : cc.AltAmount,    
Actual = ca == null ? 0 : ca.ActualAmount

I potentially nicer alternative to the existing where clause is to put the check for IsDeleted into the join:

join al in this.Context.Actuals.Where(x => !x.IsDeleted) 
  on ytl.Id  equals al.AlteredTargets.YearlyTargetId into cal

and the same for the other one. Note that this changes the meaning of the query if Actual values do exist but they're all deleted. I suspect it changes it to what you want the behaviour to be though...