title: LINQ to SQL - Left Outer Join with multiple INNER JOINS
body: <p>I am using multiple joins in a statement and Have tried to make linq-to-SQl query for this but no success.</p>
  
  <pre><code>SELECT ur.UserName, ur.LandmarkRef, lt.Date, l.Place
  FROM
      tbl_Users ur 
      LEFT OUTER JOIN tbl_LandMarks l ON ur.LandmarkRef = l.LandMarkID
      INNER JOIN tbl_LandmarkTypes lt ON l.LandmarkTypeRef equals lt.LandmarkTypeID
  
      WHERE ur.UserId == pUserId 
  </code></pre>
  
  <p>following is the Linq-to-SQL query which does not work if I use LEFT OUTER JOIN</p>
  
  <pre><code>UserDBDataContext lDc = new UserDBDataContext();
  var lU= (from ur in lDc.tbl_Users 
           join l in lDc.tbl_LandMarks on ur.LandmarkRef equals l.LandMarkID into Lmark
           from lmk in Lmark.DefaultIfEmpty()
           join lt in lDc.tbl_LandmarkTypes on lmk.LandmarkTypeRef equals lt.LandmarkTypeID 
  
           where ur.UserId == pUserId                                  
           select new { ur.UserName, ur.LandmarkRef, lc.Date, lmk.Place});
  </code></pre>
  
  <p>Here Landmark is actually an optional field so it may be NULL so in this case LEFT join does not work and return no row because of NULL value in Landmark. which works in SQL but not in LINQ-to-SQL.</p>