title: Performing 1-to-1 Left Outer Join in LINQ using Lambda expression
body: <p>I have the below LINQ query:</p>
  
  <pre><code>dbContext.NAVSummaries
          .Join(dbContext.NAVSummaries.DefaultIfEmpty(),
                  current =&gt; new
                  {
                      current.Portfolio,
                      PD = SqlFunctions.DatePart("dw", current.ValueDate) == 2 ? DbFunctions.AddDays(current.ValueDate, -3).Value :
                              SqlFunctions.DatePart("dw", current.ValueDate) == 1 ? DbFunctions.AddDays(current.ValueDate, -2).Value :
                                                                                  DbFunctions.AddDays(current.ValueDate, -1).Value
                  },
                  previous =&gt; previous == null ? null : new { previous.Portfolio, PD = previous.ValueDate },
                  (outer, inner) =&gt; new { outer, inner }
          )
          .Where(n =&gt; !dateStart.HasValue || n.outer.ValueDate.CompareTo(dateStart.Value) &gt;= 0)
          .Where(n =&gt; !dateEnd.HasValue || n.outer.ValueDate.CompareTo(dateEnd.Value) &lt;= 0)
  </code></pre>
  
  <p>There are x number of records for a given ValueDate, and x-1 number of records on it's previous business date. The NAVSummaries DbSet is self-joined on the ValueDate column with the ValueDate item matching its previous business date. However, the output results a count of only x-1. I wish to perform a left-outer join so that all the x records of the left table/collection are returned.</p>
  
  <p><strong>EDIT:</strong> There will be only one record/item matching to the previous business date. So it has to be kind of One-To-One mapping.</p>
  
  <p>NavSummary Entity :</p>
  
  <pre><code>public class NAVSummary
      {
          [Key, Column(Order = 0)]
          public string Portfolio { get; set; }
          [Key, Column(Order = 2)]
          public DateTime ValueDate { get; set; }
          public decimal BackOfficeNAV { get; set; }
          public decimal FrontOfficeNAV { get; set; }
          public decimal DifferencePercent { get; set; }
          public decimal Threshold { get; set; }
          public int ExtractId { get; set; }
          public string ExtractStatus { get; set; }
          public string PortfolioOwner { get; set; }
          public DateTime DateTimeModified { get; set; }
          public int MostCorrectNAV { get; set; }
          public virtual IList&lt;NAVComment&gt; Comments { get; set; }
          public virtual IList&lt;NAVStatus&gt; Statuses { get; set; }
          public virtual IList&lt;NAVExtract&gt; Extracts { get; set; }
          [JsonIgnore]
          [NotMapped]
          public bool IsChange { get; set; }
          [NotMapped]
          public decimal DayOverDayChange { get; set; }
          [JsonIgnore]
          [NotMapped]
          public DateTime PreviousValueDate { get; set; }
          [JsonIgnore]
          [NotMapped]
          public decimal PreviousDP { get; set; }
      }
  </code></pre>