Entity Framework - Handling a join with no records from join table

Question

I searched vigorously for an answer to this, but I was unable to find (or understand) a solution. I have a query where I am joining to another table that may or may not have associated records. Here is the query:

  var educationUniversity = result.new_educationUniversity.Select(c => new 
    { c.majorDegree, c.dateEnd, c.dateStart, c.institutionName, 
      c.degreeProgramCompletionStatus, c.institutionAddress, 
      attachmentId = c.attachmentId ?? 0, 
      fileName = c.new_attachments.fileName ?? "No Attachment"}).ToList();

The first table is "new_educationUniversity" and it holds details of a user's college or university degree. The user may or may not have uploaded an attachment (which is stored in the "new_attachments" table). The attchmentID is the primary key in the "new_attachments" table and a foreign key in the "new_educationUniversity" table. EF sees the relationship.

I am binding the results to a repeater but the code fails on the line above if there is no related attachment. Everything works fine if there is an attachment or if I remove the reference to the fileName.

Above, I am handling if the fileName is NULL (or at least I am trying to), but I suspect my issue is that the record simply doesn't exist, which is different from NULL I guess. I've tried using something like: c.new_attachments.fileName.SingleOrDefault() or DefaultIfEmpty() with no luck.

As an analogy, imagine you have a CUSTOMERS table and an ORDERS table. You want to query the following:

-customer last name -customer first name -customer most recent order Id

However, you have registered customers who have never purchased anything. I'm sure I am doing something completely noobish, so any assistance is GREATLY appreciated. Thanks!

What you're asking for is called a "left join", there are plenty of examples for how to do this, e.g. http://stackoverflow.com/questions/3404975/left-outer-join-in-linq or http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx — Michael Edenfield, Nov 24 '13 at 19:39
Found a technique that worked via this link although I don't understand it fully. Thanks! — iAwardYouNoPoints, Nov 25 '13 at 07:05

score 0 · Answer 1 · answered Nov 24 '13 at 20:06

So you have a few things going on here:

You can get a null reference exception on your reference to c.new_attachments.fileName. If new_attachments is null, then this will throw an exception. Including the null-coalescing operator (the ??) won't help, because you're trying to access the property of something which can be null.
Unless you're lazy-loading (generally bad), then there's no reason for you to be trying to create a dynamically typed object to send to your repeater. Just pass the result.new_educationUniversity object directly.

What's the solution? I'd create a partial class to add a new property to your new_educationUniversity class. Add a null-reference-safe property reference to determine the file name of the new_attachments property of new_educationUniversity. Then, bind the repeater to your new property. Something like:

public partial class new_educationUniversity {
    public String AttachmentFileName {
        get {
            if (new_attachments == null)
                return "";
            else
                return new_attachments.fileName;
        }
    }
}

I don't believe I am using lazy loading. I forget what it is called in EF when you traverse the table hierarchy via names. If EF knows my CUSTOMERS table and my ORDERS table are joined on column CUSTOMER_ID, then after I get an order var order = orders.Where(c => c.id == 3).Single(); I can still get the customer's first name with, order.customer.firstName Is it bad to do this? My SQL skills and my needs are pretty basic. Thanks! — iAwardYouNoPoints, Nov 25 '13 at 07:11
It's lazy loading if you're retrieving order.customer (to get to order.customer.firstName) AFTER you run your query to retrieve your orders. If you use an .Include() in your initial query and eager load your associated customer objects, then you're eager loading. Assuming you actually need the customers at some point, eager loading is better. — Ryan Nigro, Nov 25 '13 at 12:20

score 0 · Answer 2 · answered Nov 25 '13 at 07:18

The following line of code seems to be working.

 var educationUniversity = result.new_educationUniversity.Select
(c => new { c.majorDegree, c.dateEnd, c.dateStart, c.institutionName, 
c.degreeProgramCompletionStatus, c.institutionAddress, attachmentId = c.attachmentId ?? 0,
 fileName = (c.new_attachments == null ? "***NO ATTACHMENT***" : c.new_attachments.fileName)}).ToList();

I don't fully understand what this line means:

fileName = (c.new_attachments == null ? "***NO ATTACHMENT***" : c.new_attachments.fileName)}

I assume it says "if nothing is there replace with the string "NO ATTACHMENT" otherwise use what is in the DB"

Is this acceptable? So far, so good. Thanks!

Entity Framework - Handling a join with no records from join table

2 Answers2