title: Joint query in Linq to SQL to select all records even there are some null in the columns of foreign keys
body: <blockquote>
    <p>I'd like to take all suggestion either in C# or VB.NET.</p>
  </blockquote>
  
  <p>I have a DB diagram like the image below. I also include the database script here @ <a href="http://pastebin.com/pDC25Gkm" rel="nofollow noreferrer">http://pastebin.com/pDC25Gkm</a></p>
  
  <p><img src="https://i.stack.imgur.com/AcEtM.jpg" alt="Database diagram"></p>
  
  <p>In the Students table, CountryId and RoomId column are allowed null. Because some records do not have info about room and country yet.
  Also, some students do not have essays.</p>
  
  <p>I'm doing a joint query with all tables. I want to select all students to
  project the result like this:</p>
  
  <p><strong>Wanted query result.</strong></p>
  
  <p><img src="https://i.stack.imgur.com/cOA4y.jpg" alt="Wanted query result"></p>
  
  <p>Here's my current query that gives the result like the image below:</p>
  
  <pre><code>    Dim db As New DBDataContext
  
      Dim query = From st In db.Students _
                  Join c In db.Countries On c.Id Equals st.Id _
                  Join r In db.Rooms On r.Id Equals st.RoomId _
                  Join b In db.Buildings On b.Id Equals r.BuildingId _
                  Join es In db.Essays On es.StudentId Equals st.Id _
                  Select st, c, r, b, es
  
      'put query result into datatable
      Dim dt = New DataTable("Result")
      dt.Columns.Add("Id", GetType(Integer))
      dt.Columns.Add("Name", GetType(String))
      dt.Columns.Add("Room", GetType(String))
      dt.Columns.Add("Building", GetType(String))
      dt.Columns.Add("Country", GetType(String))
      dt.Columns.Add("Essay", GetType(String))
  
      For Each q In query
  
          dt.Rows.Add(New Object() {q.st.Id, q.st.FullName, q.r.RoomNumber, q.b.BuildingName, q.c.CountryName, q.es.Eassay})
  
      Next
  
      'bind to list view
      lvStudent.DataSource = dt
      lvStudent.DataBind()
  </code></pre>
  
  <p><strong>current query result</strong></p>
  
  <p><img src="https://i.stack.imgur.com/vuQbL.jpg" alt="Current query result"></p>
  
  <p>I got only one result back because I have (2) William NoMan record in every table. But I don't get anything about others, like (3) Sync Master who has everything but RoomId.</p>
  
  <p>What do I need to modify the query above so it will give me all students like in the wanted query image above? Thank you.</p>