Databases
StackOverflow2013
Postsdbo
POEntity Framework - Handling a join with no records from join table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEntity Framework - Handling a join with no records from join table
    text
    copied!<p>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:</p> <pre><code> var educationUniversity = result.new_educationUniversity.Select(c =&gt; 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(); </code></pre> <p>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.</p> <p>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.</p> <p>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.</p> <p>As an analogy, imagine you have a CUSTOMERS table and an ORDERS table. You want to query the following:</p> <p>-customer last name -customer first name -customer most recent order Id</p> <p>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!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload