title: LEFT JOIN in LINQ to entities?
body: <p>I'm trying out LINQ to entities.</p>
  
  <p>I have a problem with the following:
  I want it to do this:</p>
  
  <pre><code>SELECT 
       T_Benutzer.BE_User
      ,T_Benutzer_Benutzergruppen.BEBG_BE
  FROM T_Benutzer
  
  LEFT JOIN T_Benutzer_Benutzergruppen
      ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID 
  </code></pre>
  
  <p>the closest thing I've come to is this:</p>
  
  <pre><code>        var lol = (
              from u in Repo.T_Benutzer
  
              //where u.BE_ID == 1
              from o in Repo.T_Benutzer_Benutzergruppen.DefaultIfEmpty()
                  // on u.BE_ID equals o.BEBG_BE
  
              where (u.BE_ID == o.BEBG_BE || o.BEBG_BE == null)
  
              //join bg in Repo.T_Benutzergruppen.DefaultIfEmpty()
              //    on o.BEBG_BG equals bg.ID
  
              //where bg.ID == 899 
  
              orderby
                  u.BE_Name ascending
                  //, bg.Name descending
  
              //select u 
              select new
              {
                   u.BE_User
                  ,o.BEBG_BG
                  //, bg.Name 
              }
           ).ToList();
  </code></pre>
  
  <p>But this generates the same results as an inner join, and not a left join.<br />
  Moreover, it creates this completely  crazy SQL:</p>
  
  <pre><code>SELECT 
       [Extent1].[BE_ID] AS [BE_ID]
      ,[Extent1].[BE_User] AS [BE_User]
      ,[Join1].[BEBG_BG] AS [BEBG_BG]
  FROM  [dbo].[T_Benutzer] AS [Extent1]
  
  CROSS JOIN  
  (
      SELECT 
           [Extent2].[BEBG_BE] AS [BEBG_BE]
          ,[Extent2].[BEBG_BG] AS [BEBG_BG]
      FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
      LEFT OUTER JOIN [dbo].[T_Benutzer_Benutzergruppen] AS [Extent2] 
          ON 1 = 1 
  ) AS [Join1]
  
  WHERE [Extent1].[BE_ID] = [Join1].[BEBG_BE] 
  OR [Join1].[BEBG_BE] IS NULL
  
  ORDER BY [Extent1].[BE_Name] ASC
  </code></pre>
  
  <p>How can I do a left join in LINQ-2-entities in a way where
  another person can still understand what's being done in that code ?</p>
  
  <p>and most-preferably where the generated SQL looks like:</p>
  
  <pre><code>SELECT 
       T_Benutzer.BE_User
      ,T_Benutzer_Benutzergruppen.BEBG_BE
  FROM T_Benutzer
  
  LEFT JOIN T_Benutzer_Benutzergruppen
      ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID 
  </code></pre>