C# EntityFramework Join all records

Question

I created an EF Join. The frameStart has 19 records and frameEnd has 42 records, but when I do the join ( framing ), there are only 10 records. I believe there are only 10 records because it is joining records that can be grouped with the matching key. The code is below. How do I make it so that when it does the join, I get a list of all the records, even the ones that do not "match"?

public class s84_Report_FrameLabor
{
    public int CustomerID { get; set; }
    public string CustomerName { get; set; }
    public int SubdivisionID { get; set; }
    public string SubdivisionName { get; set; }
    public int LotNumber { get; set; }
    public string InstallManagerStart { get; set; }
    public string InstallManagerComplete { get; set; }
    public DateTime FrameLaborStart { get; set; }
    public DateTime FrameLaborComplete { get; set; }
    public int Duration { get; set; }

    /*
        Frame Labor Start    ------ Product ID: 26
        Frame Labor Complete ------ Product ID: 8
    */

    public static List<s84_Report_FrameLabor> getDurationReport()
    {
        using (var context = PrimaryConnection.returnNewConnection())
        {
            var frameStart = (from c in context.s84_Schedule
                             where c.ProductID == 26 && c.Completed == false
                             select new
                             {
                                 CustomerID = c.CustomerID,
                                 CustomerName = c.s84_Customer.CustomerName,
                                 SubdivisionID = c.SubdivisionID,
                                 SubdivisionName = c.s84_Subdivision.SubdivisionName,
                                 LotNumber = c.LotNumber,
                                 FrameLaborStart = c.CustomerExpectedDate
                             }).ToList();

            var frameEnd = (from c in context.s84_Schedule
                           where c.ProductID == 8 && c.Completed == false
                           select new
                           {
                               CustomerID = c.CustomerID,
                               SubdivisionID = c.SubdivisionID,
                               LotNumber = c.LotNumber,
                               FrameLaborComplete = c.CustomerExpectedDate
                           }).ToList();

            var framing = from c in frameStart
                          join e in frameEnd on new { c.CustomerID, c.SubdivisionID, c.LotNumber } equals new { e.CustomerID, e.SubdivisionID, e.LotNumber }
                          select new s84_Report_FrameLabor
                          {
                              CustomerID = c.CustomerID,
                              CustomerName = c.CustomerName,
                              SubdivisionID = c.SubdivisionID,
                              SubdivisionName = c.SubdivisionName,
                              LotNumber = c.LotNumber,
                              FrameLaborStart = c.FrameLaborStart,
                              FrameLaborComplete = e.FrameLaborComplete,
                              Duration = (e.FrameLaborComplete - c.FrameLaborStart).Days
                          };

            return framing.ToList();
        }
    }
}

Search for Entity Framework left join and you wil find the DefaultIfEmpty function. This should solve your problem. — Andre, Oct 16 '15 at 13:54
eg [Left Outer Join in LINQ to Entities](http://www.progware.org/Blog/post/Left-Outer-Join-in-LINQ-to-Entities-%28for-Entity-Framework-4%29.aspx) — stuartd, Oct 16 '15 at 13:55
Yep. Sounds like you want a left join. Check out [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq). I especially like the answer, "A significantly more readable left outer join can be written as such". — James R., Oct 16 '15 at 13:55

score 1 · Accepted Answer · answered Oct 16 '15 at 14:12

Thanks to Andre, stuartd, and James R., I found out the solution was to use the EntityFramework DefaultIfEmpty().

            var framing = from c in frameStart
                          join e in frameEnd on new { c.CustomerID, c.SubdivisionID, c.LotNumber } equals new { e.CustomerID, e.SubdivisionID, e.LotNumber } into jointable
                          from z in jointable.DefaultIfEmpty()
                          select new s84_Report_FrameLabor
                          {
                              CustomerID = c.CustomerID,
                              CustomerName = c.CustomerName,
                              SubdivisionID = c.SubdivisionID,
                              SubdivisionName = c.SubdivisionName,
                              LotNumber = c.LotNumber,
                              FrameLaborStart = c.FrameLaborStart,
                              FrameLaborComplete = z.FrameLaborComplete,
                              Duration = c.FrameLaborStart == null ? z.FrameLaborComplete == null ? (z.FrameLaborComplete - c.FrameLaborStart).Days : 0 : 0
                          };

            return framing.ToList();

C# EntityFramework Join all records

1 Answers1