Linq Left Outer Join C#

Question

I have been working with this linq query for a while. I have looked at a ton of answers on stackoverflow and beyond. I have tried many solutions and read up on just as many. The code below is 2 of my numerous attempts to create this inner join the error that I am getting is

Object reference not set to an instance of the object

Attempt A

var records = from cr in lstContactResponse
  join jn in lstJourneyNodeData on cr.GrandparentId equals jn.Id into a
  from x in a.DefaultIfEmpty()
  join j in lstJourney on x.JourneyId equals j.Id into b
  from y in b.DefaultIfEmpty()
  join ce in lstCampaignElement on y.Name equals ce.LinkedJourney into c
  from z in c.DefaultIfEmpty()
  join c in lstCampaign on z.CampaignId equals c.Id into d
  from w in d.DefaultIfEmpty()
  select new JourneyRecord
  {
      CompanyName = w.Company,
      BrandName = w.Brand,
      CampaignName = w.Name,
      Channel = z.Type,
      Wave = "",
      CampaignId = w.Id,
      ActivityDate = cr.ResponseDate,
      Activity = "",
      Unsubscribed = cr.Unsubscribed,
      Responded = cr.Responded,
      Clicked = cr.Clicked,
      Viewed = cr.Viewed,
      Sent = cr.Sent,
      Targeted = cr.Targeted,
      HardBounced = cr.HardBounced,
      SoftBounced = cr.SoftBounced,
      WasTargeted = cr.WasTargeted,
      Email = "",
      Id = "",
      CampaignElementId = z.Id,
      CampaignWaveId = "J" + x.Id,
      ContactId = cr.ContactId,
      AtTaskId = w.AtTaskId,
      LinkClicked = cr.Referrer,
      OptTopic = z.TopicId,
      DiseaseState = "",
      ElementDescription = y.Name,
      WaveDescription = x.Label
  };

Attempt B

var records = from cr in lstContactResponse
    join jn in lstJourneyNodeData on cr.GrandparentId equals jn.Id into a
    from x in a.DefaultIfEmpty()
    join j in lstJourney on x.JourneyId equals j.Id into b
    from y in b.DefaultIfEmpty()
    join ce in lstCampaignElement on y.Name equals ce.LinkedJourney into c
    from z in c.DefaultIfEmpty()
    join c in lstCampaign on z.CampaignId equals c.Id into d
    from w in d.DefaultIfEmpty()
    select new JourneyRecord
    {
        CompanyName = x == null ? null : w.Company,
        BrandName = x == null ? null : w.Brand,
        CampaignName = x == null ? null : w.Name,
        Channel = x == null ? null : z.Type,
        Wave = "",
        CampaignId = x == null ? null : w.Id,
        ActivityDate = x == null ? null : cr.ResponseDate,
        Activity = "",
        Unsubscribed = x == null ? null : cr.Unsubscribed,
        Responded = x == null ? null : cr.Responded,
        Clicked = x == null ? null : cr.Clicked,
        Viewed = x == null ? null : cr.Viewed,
        Sent = x == null ? null : cr.Sent,
        Targeted = x == null ? null : cr.Targeted,
        HardBounced = x == null ? null : cr.HardBounced,
        SoftBounced = x == null ? null : cr.SoftBounced,
        WasTargeted = x == null ? null : cr.WasTargeted,
        Email = "",
        Id = "",
        CampaignElementId = x == null ? null : z.Id,
        CampaignWaveId = "J" + (x == null ? null : x.Id),
        ContactId = x == null ? null : cr.ContactId,
        AtTaskId = x == null ? null : w.AtTaskId,
        LinkClicked = x == null ? null : cr.Referrer,
        OptTopic = x == null ? null : z.TopicId,
        DiseaseState = "",
        ElementDescription = x == null ? null : y.Name,
        WaveDescription = x == null ? null : x.Label
    };

Attempt C

var records = from cr in lstContactResponse
    join jn in lstJourneyNodeData on cr.GrandparentId equals jn.Id into a
    from x in a.DefaultIfEmpty()
    join j in lstJourney on x.JourneyId equals j.Id into b
    from y in b.DefaultIfEmpty()
    join ce in lstCampaignElement on y.Name equals ce.LinkedJourney into c
    from z in c.DefaultIfEmpty()
    join c in lstCampaign on z.CampaignId equals c.Id into d
    from w in d.DefaultIfEmpty()
    select new JourneyRecord
    {
        CompanyName = w == null ? null : w.Company,
        BrandName = w == null ? null : w.Brand,
        CampaignName = w == null ? null : w.Name,
        Channel = z == null ? null : z.Type,
        Wave = "",
        CampaignId = w == null ? null : w.Id,
        ActivityDate = cr == null ? null : cr.ResponseDate,
        Activity = "",
        Unsubscribed = cr == null ? null : cr.Unsubscribed,
        Responded = cr == null ? null : cr.Responded,
        Clicked = cr == null ? null : cr.Clicked,
        Viewed = cr == null ? null : cr.Viewed,
        Sent = cr == null ? null : cr.Sent,
        Targeted = cr == null ? null : cr.Targeted,
        HardBounced = cr == null ? null : cr.HardBounced,
        SoftBounced = cr == null ? null : cr.SoftBounced,
        WasTargeted = cr == null ? null : cr.WasTargeted,
        Email = "",
        Id = "",
        CampaignElementId = z == null ? null : z.Id,
        CampaignWaveId = "J" + (x == null ? null : x.Id),
        ContactId = cr == null ? null : cr.ContactId,
        AtTaskId = w == null ? null : w.AtTaskId,
        LinkClicked = cr == null ? null : cr.Referrer,
        OptTopic = z == null ? null : z.TopicId,
        DiseaseState = "",
        ElementDescription = y == null ? null : y.Name,
        WaveDescription = x == null ? null : x.Label
    };

Answer 1

Of course there is a way to do that in LINQ. If you have answered my questions in the comment, I would provide you the exact solution, now I'll just give you an example. The technique is different for LINQ to Objects vs LINQ to Entities, so the following apply for LINQ to Objects.

The solution is to check for null every property involved in the right side of the left join, including the further joins. Also value type properties needs to be converted to nullable (that's why it was important to have your classes - for prior C#6 code).

Here is the example:

Having the following "tables"

var t1 = new[]
{
    new { Id = 1 , Name = "A", Date = DateTime.Today },
    new { Id = 2 , Name = "B", Date = DateTime.Today},
    new { Id = 3 , Name = "C", Date = DateTime.Today},
};
var t2 = new[]
{
    new { Id = 1 , ParentId = 1, Name = "A1", Date = DateTime.Today },
    new { Id = 2 , ParentId = 2, Name = "B1", Date = DateTime.Today },
};
var t3 = new[]
{
    new { Id = 1 , ParentId = 1, Name = "A11", Date = DateTime.Today },
    new { Id = 2 , ParentId = 1, Name = "A12", Date = DateTime.Today },
};
var t4 = new[]
{
    new { Id = 1 , ParentId = 1, Name = "A111", Date = DateTime.Today },
};

Pre C#6

var query =
    from e1 in t1
    join e2 in t2 on e1.Id equals e2.ParentId into g2
    from e2 in g2.DefaultIfEmpty()
    join e3 in t3 on e2 != null ? (int?)e2.Id : null equals e3.ParentId into g3
    from e3 in g3.DefaultIfEmpty()
    join e4 in t4 on e3 != null ? (int?)e3.Id : null equals e4.Id into g4
    from e4 in g4.DefaultIfEmpty()
    select new
    {
        t1_Id = e1.Id,
        t1_Name = e1.Name,
        t1_Date = e1.Date,
        t2_Id = e2 != null ? (int?)e2.Id : null,
        t2_Name = e2 != null ? e2.Name : null,
        t2_Date = e2 != null ? (DateTime?)e2.Date : null,
        t3_Id = e3 != null ? (int?)e3.Id : null,
        t3_Name = e3 != null ? e3.Name : null,
        t3_Date = e3 != null ? (DateTime?)e3.Date : null,
        t4_Id = e4 != null ? (int?)e4.Id : null,
        t4_Name = e4 != null ? e4.Name : null,
        t4_Date = e4 != null ? (DateTime?)e4.Date : null,
    };
var result = query.ToList();

Looks ugly, but works.

C#6 - The same result is achieved by simply adding ? before any right side property accessor (repeat - including join conditions)

var query =
    from e1 in t1
    join e2 in t2 on e1.Id equals e2.ParentId into g2
    from e2 in g2.DefaultIfEmpty()
    join e3 in t3 on e2?.Id equals e3.ParentId into g3
    from e3 in g3.DefaultIfEmpty()
    join e4 in t4 on e3?.Id equals e4.Id into g4
    from e4 in g4.DefaultIfEmpty()
    select new
    {
        t1_Id = e1.Id,
        t1_Name = e1.Name,
        t1_Date = e1.Date,
        t2_Id = e2?.Id,
        t2_Name = e2?.Name,
        t2_Date = e2?.Date,
        t3_Id = e3?.Id,
        t3_Name = e3?.Name,
        t3_Date = e3?.Date,
        t4_Id = e4?.Id,
        t4_Name = e4?.Name,
        t4_Date = e4?.Date,
    };
var result = query.ToList();

Answer 2

There was no way with Linq that I was able to determine to accomplish what I had wanted to do. Therefore I went another direction with the solution to the problem.

foreach (ContactResponse cr in lstContactResponse)
    {
    ContactResponseRecord crr = new ContactResponseRecord() { 
                                                ContactId = cr.ContactId,   
                                                ActivityDate = cr.ResponseDate,   
                                                LinkClicked = cr.Referrer};


     var vJourneyNodeData = from x in lstJourneyNodeData where x.Id == cr.GrandparentId select x;
     if(null != vJourneyNodeData && vJourneyNodeData.Count() > 0)
            {
                jnd = vJourneyNodeData.FirstOrDefault();
                crr.CampaignWaveId = "J" + jnd.Id;
                crr.WaveDescription = jnd.Label;
            }

     var vJourney = from x in lstJourney where x.Id == jnd.JourneyId select x;
     if (null != vJourney && vJourney.Count() > 0)
            {
                j = vJourney.FirstOrDefault();
                crr.OptTopic = j.TopicId;
            }

     var vCampaignElement = from x in lstCampaignElement where x.LinkedJourney == j.Name select x;
     if (null != vCampaignElement && vCampaignElement.Count() > 0)
            {
                ce = vCampaignElement.FirstOrDefault();
                crr.Ccg_Id = ce.CCGId;
                crr.ElementDescription = ce.Description.ToString();
                crr.CampaignElementId = ce.Id;

      var vCampaign = from x in lstCampaign where x.Id == ce.CampaignId select x;
      if (null != vCampaign && vCampaign.Count() > 0)
                {
                    c = vCampaign.FirstOrDefault();
                    crr.ActivityDate = c.AtTaskId;
                    crr.BrandName = c.Brand;
                    crr.CampaignId = c.Id;
                    crr.CampaignName = c.Name;
                    crr.CompanyName = c.Company;
                }
            }