NullReferenceException when trying to implement LEFT JOIN with Linq Lambda expression

Question

The idea is to perform a LEFT JOIN using LINQ's lambda notation. The situation is as follows:

I have a list dbList with entries (DateTime) UploadTimestamp, (int) EntityId, and (string) FileName, generated by the following code:

var dbList = Db.Pages.Where(s => s.IsCurrent == true).
      Join(Db.Files, // (inner) table to join
           o => o.FileId, //outerKeySelector 
           i => i.Id,     //innerKeySelector
           (o, res) => new // resultSelector 
            { UploadTimestamp = o.SubmissionDate, 
              o.EntityId, 
              res.FileName
            });

Then I have another list with fields (String) FullPath, (String) FileName, (DateTime) LastModified. The code reads

var rootLocation = ConfigurationManager.AppSettings["EXPORT-FILE-LOCATION"];
string[] entries = Directory.GetFiles(rootLocation, "*.csv");
var fileList = entries.Select(x => new {
     FullPath = x,
     FileName = Path.GetFileName(x),
     LastModified = File.GetLastWriteTime(x)
});

The two tables should be connected by FileName in order to compare the two timestamps. In SQL I would write

SELECT left.*, right.* FROM fileList as left
LEFT JOIN dbList as right ON left.FileName = right.FileName
WHERE left.LastModified > right.UploadTimestamp OR right.FileName is NULL

But how do I do that that with LINQ lambda notation? For a start, I tried without the WHERE condition, inspired by How do you perform a left outer join using linq extension methods

var comboList = fileList.GroupJoin( // outer sequence
        dbList,  // inner sequence
        outerTab => outerTab.FileName, // outerKeySelector
        innerTab => innerTab.FileName, // innerKeySelector
        (x, y) => new { Links = x, Rechts = y }).
     SelectMany( x => x.Rechts.DefaultIfEmpty(),
        (x, y) => new { 
          y.FileName, DatabaseTable = y.EntityId,
          x.Links.FullPath, y.UploadTimestamp,
          x.Links.LastModified
          // Outdated = DateTime.Compare(y.UploadTimestamp, x.outerTab.LastModified) > 0 ? false : true
});

This does not work and I am currently receiving as error message:

System.NullReferenceException: 'Object reference not set to an instance of an object.'y was null.

I have a clue (only) what that means. What is the correct way to implement my SQL statement above in LINQ lambda notation? Any help is appreciated.

score 1 · Accepted Answer · answered Dec 04 '19 at 11:46

Looking at the error it appears y was null.

Make sure y is not null before dereferencing.

SelectMany( x => x.Rechts.DefaultIfEmpty(),
    (x, y) => new { 
      Filename = y?.FileName, 
      DatabaseTable = y?.EntityId,
      x.Links.FullPath, 
      UploadTimestamp = y?.UploadTimestamp,
      x.Links.LastModified

This does mean the resulting objects can have FileName, DatabaseTable and UploadTimestamp as null, so consumers of that should still check for nulls on those before using them as well.

Simple solution to just add some clever question marks, but it saved my day! — B--rian, Dec 04 '19 at 16:29

NullReferenceException when trying to implement LEFT JOIN with Linq Lambda expression

1 Answers1